In Microsoft Excel there are often many different ways to create a solution. The best solution is often subjective based on whether we emphasize simplicity or efficiency. Here is a good example:
Sample Dataset & Requirements
We want the 1st ‘Code’ for Miami when ‘Description’ is not blank.
We don’t want the ‘Code’ in row 2 (‘Description’ is blank).
The ‘Code’ in cell C8 is what we want.
It’s easy to see the correct answer but imagine 30000 rows of data! We would need an automated solution. This post will focus on formula solutions.
Sort The Data!
Yes, this would make it easier but let’s assume the current order of the data has some significance and we can’t sort it.
Factors to Consider
Factors to consider when creating a solution:
- Who will be using/updating this? What is their skill level?
- Can we add a helper column? It reduces complexity but also increases file size.
- Can we build a single yet complex array formula to solve it?
Excel File & YouTube Video
Download my Excel file here and watch my video here.
Vote For Your Preferred Solution
Which solution do you prefer? Please leave a comment below.
- (a) vlookup array
- (b) index & match array
(c) lookup (doesn’t work!)- (d) helper & vlookup
- (e) helper & vlookup (2)
- (f) helper & index match
- (g) index & array
About Me
My name is Kevin Lehrbass. I’m a Data Analyst working in Toronto.
This is me inside of Excel (read this post).
This is my personal blog about Microsoft Excel. A place to explore possibilities and share ideas.
Check out my recommended Excel training.
LOOKUP works this way: =LOOKUP(1,1/(($B$2:$B$11″”)*($A$2:$A$11=K2)),$C$2:$C$11)
I just added “1/” (without quotes) at the start of the lookup vector argument. The “1” in “1/” works to find the first match. If we add “2/” instead of “1/”, the “2” in “2/” works to find the last match. In both cases, #DIV/0! errors are ignored. For easy recall, “1” means first and “2” means last.
Thank you Robert! Your lookup correction works perfectly! I totally missed this. Thanks for finding my error!
Cheers, Kevin
It works for selection “Miami”…but it can’t get the lookup to work for either ‘Baltimore’ or ‘Toronto’. Can you?
I cannot also understand why the formula doesn’t work for Baltimore or Toronto. But because LOOKUP is my favorite function, my revised formula is this:
=LOOKUP(1,1/(1/ROW($A$2:$A$11)=
MAX(INDEX((1/ROW($A$2:$A$11))*($B$2:$B$11″”)*($A$2:$A$11=K2),0))),
$C$2:$C$11)
In this revised formula, 1/ROW($a$2:$A$11) acts as the imaginary helper column. This time, the formula works also for Baltimore or Toronto. By the way, we arrive at the same answer even if the lookup value argument is changed to 2. The reason is that the formula will only have 1 maximum value in the array argument of the index function. Thanks for bringing out the creativity in me, Kevin!
Hi Robert,
Thanks for the expertise on the lookup function! I like your helper column idea! I was also really puzzled why it didn’t work for Baltimore and Toronto. Lookup really is an amazing function. It might not be my favorite like it is for you but the last couple of years I’ve really been learning how flexible and fast it is! Lookup, Aggregate and Textjoin are all functions that I need to keep studying. BTW, do you have an Excel blog? If not you should start one!
Cheers,
Kevin
Hi Kevin,
Here is my solution,
=INDEX($C$2:$C$11,AGGREGATE(15,6,(TREND(IF({1},2*COUNTA($C$2:$C$11)+1,$C$2:$C$11),,,0)/3)/(($A$2:$A$11=K2)*($B$2:$B$11″”)),1))
Hi Kevin,
Here is my solution,
=INDEX($C$2:$C$11,AGGREGATE(15,6,(TREND(IF({1},2*COUNTA($C$2:$C$11)+1,$C$2:$C$11),,,0)/3)/(($A$2:$A$11=K2)*($B$2:$B$11″”)),1))