An odd lookup problem [Formulas]

Posted on December 6th, 2016 in Excel Challenges , Learn Excel - 18 comments

Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like this.

odd-lookup-problem

And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.

Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).

So how would you write the lookup formula?

Odd Lookup formula:

Let’s assume your data starts at B4 and goes for 200 cells (ie 100 employee and 100 manager names), and the employee name you want to lookup is in cell F4.

We can find the position of the employee using below MATCH formula. (Array formula, so press Ctrl+Shift+Enter after typing it)

=MATCH(F4,T(OFFSET($B$4,ROW($A$1:$A$100)*2-2,,1,1)),0)

This will give us the position of employee (ie 3 for Andrea Nichols in the sample data image above).

Once we have the position, we can use below INDEX formula to get the manager’s name.

=INDEX($B$4:$B$203,position*2)

How do these formulas work?

The INDEX formula is fairly obvious. So let’s dig deep in to the MATCH formula.

  1. First we extract all the odd cells in the range B4:B203 using OFFSET($B$4,ROW($A$1:$A$100)*2-2,,1,1)) part. The ROW(A1:A100) portion generates an array of numbers from 1 to 100 which we then convert to even numbers using simple arithmetic.
  2. We then use T() formula to convert the odd cell values in to an array of text values.
  3. Finally MATCH() looks for the employee name in F4 against this list to find the matching position.

Please refer to OFFSET tutorial & INDEX formula tutorial to understand the syntax and array usages.

How to make these formulas generic:

In our formulas above, we use a fixed range A1:A100 to generate the even numbers. If you have different sized list, you can use below generic version of the ROW formula.

Assuming your list is named list:

Replace the ROW() formula above with below version:

ROW($A$1:OFFSET($A$1,COUNTA(list)/2,,))

Download example workbook

Click here to download example workbook for odd lookup problem. Play with the formulas in cells F5 & F6 to learn more.

How would you lookup odd values…

Bad data is everywhere. Recently, I have come across a data set that is precisely like this. I ended up using the above pattern to find the answer quickly.

What about you? How would you lookup odd values? Please share your approach in the comments section.

Even more odd data problems? We got you covered

Don’t let dirty data drag you down. Check out below resources to learn more.

 

18 Responses to “An odd lookup problem [Formulas]”

  1. mma173 says:

    You can use
    {=INDEX(Range, MATCH(1,((Range=Criteria)*(MOD(ROW(INDIRECT("1:"&COUNTA(Range))),2)=0)),0))}

  2. Desk Lamp says:

    My first instinct would be to do something like mma173. But rather than copying that solution I'll offer a non-volatile, non-CSE solution:

    =INDEX(B:B,SUMPRODUCT((MOD(ROW(B4:B203),2)=0) * (B4:B203=F4) * ROW(B4:B203))+1)

    This only works if there are no duplicate employees (which there doesn't seem to be)

  3. Leonid says:

    My version is a slight variation of Desk Lamp's version with ISODD and some guards for case when new rows inserted above the table:
    =INDEX($B$4:$B$203,1+SUMPRODUCT(($B$4:$B$203=$F$4)*(ROW($B$4:$B$203)-ROW($B$4)+1)*ISODD((ROW($B$4:$B$203)-ROW($B$4)+1))))

  4. Wow! It seems like this is unstack data week! http://www.myspreadsheetlab.com/unstacking-data-with-oz-du-soleil/
    So many different ways that data can be stacked.
    Thanks for another example Chandoo and great solutions!
    Cheers,
    Kevin

  5. Anand says:

    This is off topic discussion.

    I need to know what is the name of font used in writing below text in your query.

    We need a ODD lookup
    So how would you find the boss for a given employee?

  6. Matt says:

    I would put a 1 next to the name, 2 next to the manager, copy this down the entire list then filter and split out. 1's in one column, 2's in another column.

    Then can just use simple vlookup!

  7. Elias says:

    One more,

    =LOOKUP(2,1/((B4:B203=F4)*(MOD(ROW(B4:B203),2)=0)),B5:B204)

    Regards

  8. Sudhir Gawade says:

    If data start from A1 then I will use below formula.

    =IF(ISODD(ROW()),INDEX($A:$A,MATCH(A1,$A:$A,1)+1,0),"")

    • Sudhir Gawade says:

      Or else below two formulas in I3 and J3 and drag formulas

      cell I3 =INDEX($B$3:$B$203,EVEN(ROW(B1)*2))
      Cell J3 =INDEX($B$3:$B$203,ODD(ROW(B1)*2))

  9. David N says:

    The solution Chandoo provided in his file will not break if the list were moved down one row -- i.e. all currently even rows become odd and vice versa. Except for Leonid, the alternate solutions proposed so far break in that situation. The robust nature of the Chandoo and Leonid formulas is that they rely only on the alternating pattern/position within the list instead of the explicit even or odd value of the row numbers.

    However, the slight negatives to Chandoo's method are that it's both volatile (uses an OFFSET) and requires array (CSE) execution. Leonid's solution is again neither of those things. I say slight because the negativity of those aspects is largely a matter of personal preference. But if you prefer non-volatile, non-array formulas, then this one will work and surpasses Leonid's only in being shorter.

    =INDEX(B4:B20,MATCH(1,INDEX(1/((B4:B20=F4)*ISODD(ROW(B4:B20)-ROW($B$3))),0),0)+1)

    It uses the same division logic and match/lookup as Elias (one over the product of the conditions), mimics Leonid in using ISODD with subtraction to make sure the name is found at an odd position versus an odd row, and leverages an extra INDEX to make that resulting array palatable to the MATCH without needing CSE execution.

    • David N says:

      Of course my B4:B20 ranges should be extended to cover the full list (e.g. B4:B203). I had them truncated to make stepping through this sample calculation a bit easier with something like the Evaluate Formula utility.

  10. MF says:

    Whenever possible, I will use a helper column to make it work in a simple way. 🙂

    • Sunny Kow says:

      I'm with MF with helper columns.

      Example:
      Cell C4 =IF(ISEVEN(ROW()),B4,"") 'Employee
      Cell D4 =IF(ISEVEN(ROW()),B5,"") 'Boss
      copy all the way down.

      Now do a VLOOKUP or INDEX/MATCH on column C to get the boss's name.

  11. Leonid says:

    And, who is the boss of Marilyn Allen? - Andrea Nichols 🙂

  12. EXCELent Bacon says:

    Chandoo, my man, any thoughts on expanding your staff and expanding the scope of this site? Like, daily updates, various sections, columns, etc.?

  13. Nitin verma says:

    {=INDEX($B$4:$B$203,IF(ODD(IF($B$4:$B$203=F4,1,0)*ROW($B$4:$B$203)),ROW($B$4:$B$203)+2,0))}

    Use with CSE

  14. Hugo says:

    what's wrong with:
    =index(b4:b204, match(f4,b4:b204;0)-1)

Leave a Reply