• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup

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
 

Attachments

  • TestSheet.xlsm
    153.8 KB · Views: 7
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 ....
 

Attachments

  • TestSheet-DME fix.xlsm
    141.8 KB · Views: 2
@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.
 
@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 ...
 
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
 
Back
Top