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 at McKinsey & Company.

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.

Post a comment