fbpx
Search
Close this search box.

An odd lookup problem [Formulas]

Share

Facebook
Twitter
LinkedIn

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.

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

21 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)

  15. Peter B says:

    A different approach in that it does not use direct cell referencing.

    The manager's name is given by
    = INDEX(Table1[data], manager.recordNum )

    In turn, the record number for the manager refers to the formula
    = MIN( IF( MOD( k, 2 ) * ( Table1[data] = name ), k ) ) + 1
    where k is the array of record numbers starting at 1. To calculate 'k', define it as referring to

    = ROW(Table1) - ROW(Table1[#Headers])

    The plusses are: no CSE, no volatile functions, no dependence on data location. The minus: the average Excel user will throw a fit.

  16. Sachin says:

    =MATCH(F4,$B$4:$B$203,0)

    =INDEX($B$4:$B$203,F5+1)

  17. Peter B says:

    This blog cropped up after a long gap for some reason. Last time the thoughts that came to mind were 'how would I deal with an alternating list?'.
    What I missed out on was fully appreciating the elegance of the solution we were offered. Reworking to use names rather than direct referencing, I defined a zero-based index k from 0 to n using the data array itself as a range
    n: = ROWS(Data)/2 - 1
    k: = ROW( heading : INDEX(Data, n ) ) - ROW(heading)
    Then the following OFFSET formulas behaved perfectly
    employeeList: = T( OFFSET( Data, 2*k, , 1, 1 ) )
    managerList: = T( OFFSET( Data, 2*k+1, , 1, 1 ) )
    to give sensible data structures.
    The employee data is sorted alphabetically so
    = LOOKUP( employee.name, employeeList, managerList )
    captures the intent of the formula perfectly.

    Out of curiosity, I applied the formula 2000 times to track the relationships for each employee to identify their 10th level boss. This allowed me to identify a few changes to the data needed to create an acyclic graph. Of greater relevance here it also demonstrated that the volatility of the OFFSET function is not an issue for problems on this scale.

Leave a Reply