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

Vlookup formula to look part of lookup value -Please Help

S

samtheman

New Member
Good Morning,
I reallly need vlookup (or other formula) help.

I have following Table Array:
AB 12
ABTS 19
DL 30
NL 15
XL 7

by using above table array, I want follow :
Lookup Values What I want
AB Dress 12
DL Pant 30
Shirt XL 7
Trouser ABTS Line 19
Socks NL Straight 15
Shoe AB 12

I would be very grateful if I get correct formula.
Thanking you,
Sam
 
If you have time, could you please briefly explain formula & sub formula (formula forensics) of the above solution?
 
SEARCH function finds a string in the other string and returns position. It is not case sensitive. If case sensitive search is required then FIND should be used in place of SEARCH.

To ensure that exact word is being matched each search word is prefixed and suffixed by " " using concatenation operator (&) like so:
" "&$A$2:$A$6&" "
Note that inside LOOKUP we can supply range array i.e. A2:A6 in the first argument of SEARCH function. Since in the search string can be in the beginning or end of the main string we pad it up with " ".
" "&A11&" "
This ensures that the SEARCH doesn't fail in such places.

So now inside LOOKUP it returns an array of Error Values and Numeric Position where match is found. In following formula:
=LOOKUP(99,SEARCH(" "&$A$2:$A$6&" "," "&A11&" ",1),$B$2:$B$6)
99 is assumed as maximum length of the string that we are working with. Some people use 2^15 which corresponds to maximum characters in a single cell.

At this point, I will recommend you use Formula Evaluate option to see how it unfolds and interpret using above information. Hope this helps.
 
Last edited:
Thank you very much for your explanation.
I believe I might able to use lookup & search functions right way in future.
I really appreciate.

Thanking you,
Sam
 
You must log in or register to reply here.
Back
Top