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.
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.
- 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. TheROW(A1:A100)portion generates an array of numbers from 1 to 100 which we then convert to even numbers using simple arithmetic. - We then use
T()formula to convert the odd cell values in to an array of text values. - 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.
- Introduction to Excel VLOOKUP formula
- Multi-condition lookups
- VLOOKUP second or third (or nth) match
- Lookup the answer in matrix
- Lookup discounted pricing tier
- Range lookup – find out which range contains the answer
21 Responses to “An odd lookup problem [Formulas]”
You can use
{=INDEX(Range, MATCH(1,((Range=Criteria)*(MOD(ROW(INDIRECT("1:"&COUNTA(Range))),2)=0)),0))}
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)
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))))
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
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?
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!
One more,
=LOOKUP(2,1/((B4:B203=F4)*(MOD(ROW(B4:B203),2)=0)),B5:B204)
Regards
If data start from A1 then I will use below formula.
=IF(ISODD(ROW()),INDEX($A:$A,MATCH(A1,$A:$A,1)+1,0),"")
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))
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.
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.
Whenever possible, I will use a helper column to make it work in a simple way. 🙂
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.
And, who is the boss of Marilyn Allen? - Andrea Nichols 🙂
Good callout Leonid. I hadn't noticed that this mocked up data had them managing each other. That's funny!
Chandoo, my man, any thoughts on expanding your staff and expanding the scope of this site? Like, daily updates, various sections, columns, etc.?
{=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
what's wrong with:
=index(b4:b204, match(f4,b4:b204;0)-1)
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.
=MATCH(F4,$B$4:$B$203,0)
=INDEX($B$4:$B$203,F5+1)
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.