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.
Excel 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.
Try the Challenge
To try the Excel lookup 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:
- 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.
. Keep scrolling
Here is the formula that I used to solve the Excel lookup formula challenge. The formula is array-entered, with Ctrl+Shift+Enter.
For a brief description of how the formula works, look on the SolutionsA sheet in the Lookup Challenge Solutions workbook.
Basically, it creates a string that contains the customer name, and looks for that in the imported data cell. If the customer name is found, MATCH returns the position of that customer in the lookup list, and INDEX returns the code from column B.
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:
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 Excel Lookup Formula 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