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.

codelookup01

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:

  • 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.

.

.

.

. Keep scrolling

.

.

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

=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.

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.

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

___________________

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

___________________

Save

You may also like...

9 Responses

  1. David N says:

    It doesn’t look like your FN0052 file is posted yet, so my solution may be a duplicate.
    This one does NOT require array entry: =INDEX($B$2:$B$4,MATCH(1,INDEX(SIGN(SEARCH($A$2:$A$4,D2)),0),0))
    Or it could be shorter if array entry is acceptable: =INDEX($B$2:$B$4,MATCH(1,SIGN(SEARCH($A$2:$A$4,D2)),0))
    And even a bit shorter if SIGN isn’t used: =INDEX($B$2:$B$4,MATCH(99^9,SEARCH($A$2:$A$4,D2),1))

  2. Shairal says:

    Hi Debra,
    For SolutionsB, will you please explain the:
    LOOKUP(2^15, ….
    I see the result is 32768, but I don’t understand how it’s used in the formla.
    Thanks! and great post!

    • Daniel H says:

      Hi Shairal
      LOOKUP is quite a tricky formula indeed; my understanding is the following.
      SEARCH($A$2:$A$4,$D2) will return an unidimensional matrix (a vector) of ERRORs and only one integer with the position of the first character inside the cell in which the coincidence is positive.
      2^15 is just a big number, always greater than any integer the previous formula may return.
      LOOKUP will look inside the vector of ERRORs and the integer for the first value equal or below this big number. This function skips ERRORs (that’s its magic) and there’s only one integer, so it will return the position of the only integer inside this vector.
      The last part of the formula is a comparison vector. Given its position (“n”), LOOKUP will return the “n” element of the comparison vector.

      I have another solution, very similar, but perhaps it’s easier to understand.
      = LOOKUP(TRUE,SEARCH($A$2:$A$4,D2)>0,$B$2:$B$4)
      SEARCH($A$2:$A$4,D2) will return a vector of ERRORs and one integer. Adding “>0” the integer will be converted to TRUE (a Boolean) when the condition is met.
      The first argument of LOOKUP is TRUE, so it will look for the first occurrence of TRUE inside this vector. There’s only one TRUE so its position will be given back.
      Once you get the position “n”, the LOOKUP formula will extract the “n” element in the comparison range ($B$2:$B$4).

  3. I was pretty close

    =INDEX($B$2:$B$4,MATCH(FALSE,ISERROR(FIND($A$2:$A$4,D2)),0))

    array entered

  4. pmsocho says:

    So many ways to have fun with Excel. Here is another way:
    =INDEX(B$2:B$4,MATCH(1,COUNTIF(D2,”*”&A$2:A$4&”*”),0))

  5. Peter Bartholomew says:

    I impose a different programming style on my solutions and prize readability ahead of brevity, so my solutions are somewhat different in appearance.
    Because the imported data is a list and likely to be extended I did not use a single array formula but rather matched each customer address individually. So, defining
    CustomerAddress: = ImportedData[@CustomerAddress]
    one determines a Boolean array that matches the list of customer names against the particular address
    matches?: = ISNUMBER( FIND( CustomerList, CustomerAddress) )
    To return the row number of the first match
    codeMatch: = MIN( IF( matches?, listRow ) ).

    Finally the code is returned by a worksheet formula
    = INDEX( CodeList, codeMatch )
    It is not necessary to array enter the formula because all the array calculation has already been carried out using named formulas where array arithmetic is the default.

Leave a Reply

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