lOOKUP USING TEXT ISSUE

[mbtran2003]

Hi All,

I'm trying to do a lookup from name text to retrieve client ID #. It doesn't seem to work. What other function can I can use to retrieve the ID instead of manual keying it in. I have thousands of client ID to retrieve. The raw data sheet is provided to me with company name rather than our internal client id. Really appreciated your suggestion.

[DonkeyOte]

LOOKUP function expects the lookup_vector to be listed in Ascending order - this is not the case with your data (C2:C11) and also LOOKUP is unlikely to return the correct results in this context...

It's not really clear what you're expected results should be... can you post a revised sample ?

For ex. a rough approach might be to find the first entry in C2:C11 which appears in Dx string, eg:

E2:
=INDEX($B$2:$B$11,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($C$2:$C$11,$D2)),0),0))

but even then you will not find matches for all...

[seckert]

The lookup will work fine if you can standardize the names. (i.e. "Best Buy 2" and "BestBuy2" are not comparable text strings. ) and sort your master table by "C_Name" . Or you can copy the Client ID to the right of the Name and use vlookup. You won't have to resort, but you will still need to standardize the names.

[mbtran2003]

The lookup will work fine if you can standardize the names. (i.e. "Best Buy 2" and "BestBuy2" are not comparable text strings. ) and sort your master table by "C_Name" . Or you can copy the Client ID to the right of the Name and use vlookup. You won't have to resort, but you will still need to standardize the names.

--------------------------------------------------------------------------------

First of all, thanks for the reply to my question....


__VLOOKUP(D2,$B$2:$C$11,2,FALSE)
If I use Lookup Value (Search for the first 4 to 5 character within the text string would that work....something like

**Vlookup(search(,d2,4),$b$2:$c$11,2,false)....

End result that I would need is Client ID. I'm hoping the somekind of complex function would enable to search for Client ID.

[mbtran2003]

__VLOOKUP(D2,$B$2:$C$11,2,FALSE)
If I use Lookup Value (Search for the first 4 to 5 character within the text string would that work....something like

**Vlookup(search(,d2,4),$b$2:$c$11,2,false)....

End result that I would need is Client ID. I'm hoping the somekind of complex function would enable to search for Client ID.

[martindwilson]

i think you're probably have to some data cleansing, the first 2 are an example of the problem you are up against
BESTBUY INC
BEST BUY 2
to match against
BESTBUY
BESTBUY2
matching first 4 characters woud match both
matching first 5 would never find BESTBUY2