Smart Vlookup

[sungen99]

In the attached file I am trying to get Col B (via a formula) to find the appropriate color code (Col I) but using the data in Col A.

I know how to do exact lookup no issue, but when I try to use TRUE instead of FALSE i still get an NA response.

I can only assume that there might be a cheater cell needed someplace to for the life of me I cant figure it out.

Thank you for your help.

[Glenn Kennedy]

In your real data, is it always:

PRODUCT154

or even, is it always PRODUCT123 Product plus 3 digits...

[Glenn Kennedy]

No reply...

This ALMOST works (Green cells) :

=IFERROR(LOOKUP(1000,SEARCH(MID(LEFT(A2,LEN(A2)-4),SEARCH(" ",A2,SEARCH("Product",A2))+1,255),$I$2:$I$25),$J$2:$J$25),"")

However, it does not match "Canvas Ant Beige" with "Canvas Antique Beige".

I think you have two choices here. EITHER use a bit of VBA to find a partial match (I can help here... up to a point) OR you can download the free microsoft add-in "fuzzy lookup". Use it to match the result of the new formula in the beige cells:

=MID(LEFT(A2,LEN(A2)-4),SEARCH(" ",A2,SEARCH("Product",A2))+1,255) with your code list.

Let me know what you think.

[sungen99]

VBA would always be better if you could help. i wont be the only person using this so it needs to work everytime.

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)