excelexcel-formulaexcel-2013

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

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

Example

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

Solution

  • 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( )

    enter image description here

    • 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]

    enter image description here

    • 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:

    enter image description here

    =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)),"")

    enter image description here

    enter image description here