Retrieve 1st Code for Selected City When Description is not blank

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.

6 Comments Retrieve 1st Code for Selected City When Description is not blank

  1. Robert H. Gascon

    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.

    Reply
    1. Kevin Lehrbass

      Thank you Robert! Your lookup correction works perfectly! I totally missed this. Thanks for finding my error!
      Cheers, Kevin

      Reply
    2. Kevin Lehrbass

      It works for selection “Miami”…but it can’t get the lookup to work for either ‘Baltimore’ or ‘Toronto’. Can you?

      Reply
      1. Robert H. Gascon

        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!

        Reply
        1. Kevin Lehrbass

          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

          Reply
  2. Meganathan Elumalai

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *