Excel Lookup Formula Challenge 20171026

Here's an Excel Lookup Formula challenge to get your brain fired up. Can you solve it without doing a Google search? The problem details are shown below, and you can download the sample workbook. It has the sample data, and there are solutions too, on a different worksheet.

The Lookup Formula Challenge

The problem is based on an email question that someone sent to me recently. They had a solution, but it was a long, complicated formula. Could I come up with something that was more efficient? Well who can resist a challenge like that?

  • In columns A:B, there is a lookup table, with a list of customers and their ID codes.
  • In column D, there is imported data. There is a customer name in each cell, but it's not in a consistent place.
  • Create a formula in column E, to get the correct customer code for each imported record.

codelookup01

Try the Challenge

To try the challenge, build your own sample based on the screen shot above, or go to the Excel Sample Files page on my Contextures website, and download my Excel file.

In the Functions section, look for FN0052 - Lookup Challenge Solutions

In the workbook you'll find:

  • Instructions
  • challenge sheet
  • sheet with clues
  • Solution sheets

What formula did you build? Was is similar to my original formula, or like one of the other, that my newsletter readers submitted?

My Solution to the Challenge

I enjoyed solving the formula challenge, and came up with a formula that was much shorter than the original one that was sent to me. But, I figured that the smart people who read my Excel newsletter could find a solution that was better than mine!

Scroll down if you'd like to see my solution, but stop reading, and come back later if you're still working on the challenge.

.

.

.

.

.

.

.

.

.

Here is the formula that I used. It is array-entered, with Ctrl+Shift+Enter.

=INDEX($B$2:$B$4, MATCH(1,IFERROR(
SEARCH("*"&$A$2:$A$4&"*",D2,1),0),0))

For a brief description of how the formula works, look on the SolutionsA sheet in the Lookup Challenge Solutions workbook.

Non-Array Formulas

As I expected, other people had better solutions for this problem. Some people used the LOOKUP function in their solutions, and those formulas didn't need to be array-entered. For example:

=IFERROR(LOOKUP(2^15,SEARCH($A$2:$A$4,$D2),$B$2:$B$4),"")

There are a few others on the SolutionsB sheet in the download file. And if you find a different solution, please share it in the comments below.

Another Formula Explained

Kevin Lehrbass enjoyed the challenge, and made a video to explain his solution. The sound is a bit low, so adjust your speaker, if you can't hear it clearly.

Here is Kevin's formula:

  =INDEX($B$2:$B$4, MIN(IF(ISNUMBER(SEARCH($A$2:$A$4,D2)), ROW($A$2:$A$4)-ROW($A$2)+1,"")))

Kevin said, "I had to add the ROW - ROW part (in case someone would insert a row above and break my array)."

Download the Workbook

In case you missed the download links earlier, here's how you can get the sample file. Go to the Excel Sample Files page on my Contextures website, and download my Excel file. In the Functions section, look for FN0052 - Lookup Challenge Solutions

___________________

Excel Lookup Formula Challenge http://blog.contextures.com/

___________________

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.