How to lookup and return multiple values alternating (50/50)

Question

I have a sets of 2 break times and i want to lookup values for a big schedule and getting one of two types of break time with a 50/50 ratio.

Example

I know how to vlookup, but it always returns me only the first or second value, not alternating.

Answer 1

If i have understood clearly and not mistaken with the required output, then you might need this as an output using the following formula: DOUBLE VLOOKUP( ) with an IF( ) Logic with COUNTIF( )

---

• Formula used in cell E2

=IF(COUNTIF(D$2:D2,D2)>2,VLOOKUP(D2,$A$2:$B$9,2,1),VLOOKUP(D2,$A$2:$B$9,2,0))

---

Formula needs to be filled down for the rest of the cells. Also make sure to change the cell references and ranges accordingly as per your suit.

---

We can make it bit shorter here is how. IF( ) Logic with COUNTIF( ) is used in for the optional param [range_lookup]

---

• Formula used in cell E2

=VLOOKUP(D2,$A$2:$B$9,2,IF(COUNTIF(D$2:D2,D2)>2,1,0))

---

Revised Approach Two :

=VLOOKUP(D2,$A$2:$B$9,2,COUNTIF(D$2:D2,D2)>2)

---

Revised Solution Based On OP's Comments:

---

=IFERROR(IF(COUNTIFS($D$2:$D$19,D2)<=2,
  VLOOKUP(D2,$A$2:$B$9,2,COUNTIF(D$2:D2,D2)>1),
  VLOOKUP(D2,$A$2:$B$9,2,COUNTIF(D$2:D2,D2)>2)),"")

---

---

---

Answer 2

It seems you're repeating the range 2 times and the outcome is sorted by shift, then breaktimes.

You could use VSTACK and SORT to get the desired result, in this case =SORT(VSTACK(A2:B9,A2:B9),{1,2})

If you want a changeable number of repeats, you could make use of REDUCE:

=LET(range,  A2:B9,
     rw,     ROWS(range),
     repeat, 2,
SORT(REDUCE(range,SEQUENCE(repeat-1),
     LAMBDA(r,rpt,
            VSTACK(r,TAKE(r,rw)))),
     {1,2}))

You can then change repeat to whatever number of times you need it repeated.

I see Excel 2013 is tagged. This requires Office 365. For older Excel refer to Mayukh's answer