Lookup

[Frank Bacchus]

Attached is a Sheet with four significant columns - Column B (a description) which may occur multiple times. Then Column H containing the same description as in Column B, no repeating values, and Column I containing a "YES" or "NO". Next is column "E", labeled "Flag" (in the sheet) which returns the value from Column I based on the lookup of the description from Column B. I am using this formula : =IFERROR(IF(ISBLANK(A2),"NO",LOOKUP(2^15,SEARCH($H$1:$H$366,B2),$I$1:$I$366)),"YES")

All the results seems to be correct except for the results from B16. The results indicates "NO" but it should be "YES'.

I cannot understand why the results is coming back incorrect. Can you? I am attaching a sample file. Thanks in advance.

frank

 

[David Evans]

Hi Frank -

I managed to solve the issue with B16 by way of sorting the Lookup table $H$1:$I$366 alphabetically .... why it solves it, I'm not sure, but it does - greater minds than mine will be along shortly to explain, I'm sure.

I copied your check formula in Col F which appears to be returning an #N/A in a couple of places, but your IFERROR will hide that ....

 

[David Evans]

Take a look at https://support.microsoft.com/en-us/kb/181212

"LOOKUP requires that the first column of the vector (or the first column or row for the array form) is sorted in ascending order."

 

[r1c1]

@Frank Bacchus

I am not sure why you would use a complex formula like,

=IFERROR(IF(ISBLANK(A2),"NO",LOOKUP(2^15,SEARCH($H$1:$H$366,B2),$I$1:$I$366)),"YES")

When the obvious one is better -

=VLOOKUP(B2,$I$1:$J$366,2,FALSE)

That said, the reason for your error is below:

• SEARCH formula finds first parameter in the second one.
• So when a cell has just "SEARS ROEBUCK", SEARCH returns 1, because it can find the same text in the value in cell B16.

In other words, your usage of SEARCH($H$1:$H$366,B2) will yield incorrect results. It so happened that you spotted only one such wrong answer. There are more,

for example - B11 - HARMONY CLEANERS... seems to be wrong.

 

[David Evans]

@Frank Bacchus

I am not sure why you would use a complex formula like,

=IFERROR(IF(ISBLANK(A2),"NO",LOOKUP(2^15,SEARCH($H$1:$H$366,B2),$I$1:$I$366)),"YES")

When the obvious one is better -

=VLOOKUP(B2,$I$1:$J$366,2,FALSE)

That said, the reason for your error is below:

• SEARCH formula finds first parameter in the second one.
• So when a cell has just "SEARS ROEBUCK", SEARCH returns 1, because it can find the same text in the value in cell B16.

In other words, your usage of SEARCH($H$1:$H$366,B2) will yield incorrect results. It so happened that you spotted only one such wrong answer. There are more,

for example - B11 - HARMONY CLEANERS... seems to be wrong.

Simplify, simplify, simplify - a great mantra ...

 

[Frank Bacchus]

Take a look at https://support.microsoft.com/en-us/kb/181212

"LOOKUP requires that the first column of the vector (or the first column or row for the array form) is sorted in ascending order."

Thanks Dave

 

[Frank Bacchus]

Thanks Dave

Thanks you. Appreciate it.

@Frank Bacchus

I am not sure why you would use a complex formula like,

=IFERROR(IF(ISBLANK(A2),"NO",LOOKUP(2^15,SEARCH($H$1:$H$366,B2),$I$1:$I$366)),"YES")

When the obvious one is better -

=VLOOKUP(B2,$I$1:$J$366,2,FALSE)

That said, the reason for your error is below:

• SEARCH formula finds first parameter in the second one.
• So when a cell has just "SEARS ROEBUCK", SEARCH returns 1, because it can find the same text in the value in cell B16.

In other words, your usage of SEARCH($H$1:$H$366,B2) will yield incorrect results. It so happened that you spotted only one such wrong answer. There are more,

for example - B11 - HARMONY CLEANERS... seems to be wrong.

Thanks for the response. I see your point on simplification. What is interesting to me is that I took the content in B2 "SEARS ROEBUCK" and did a CTRL F. It found two instances - the one in B2 and the one in the array "$H$1:$H$366". Does the array for a "search function need to be sorted in a specific form?

frank