How to make VLOOKUP work when lookup value and value in array table are not exact match.

[Sean15]

Hello

I have a worksheet with several hundred rows of name and numbers in column K. I want to be able to assign a code in column L to each name and number in column K. I figured the simplest way is to set up an array table and use VLOOKUP to return the assigned code. The problem is data in column K changes constantly (slight variation though) so I am not sure how to set the array table or how to make VLOOKUP work.

Example
K2 = dolrtree 4766 0004745

But the next time I receive worksheet,

K2 = dolrtree 4856 0004896

See name in K2 is constant but the number changes.

The array table I believe could be created using name, excluding number. So an example of array table would be:

A2 = dolrtree B2 = 75150 (column B would contain assigned codes)

How can I make the VLOOKUP work when lookup value in K2 is differ from value in array table? Am I doing in the right way?

Regards,

Sean

 

[Andrew Poulsom]

Does this work for you?

=VLOOKUP(LEFT(K2,FIND(" ",K2)-1),A:B,2,FALSE)

 

[JoeMo]

If the names are always consistent and are always followed by a space and then the numbers you can use something like this to find the code:

=VLOOKUP(LEFT(K2,SEARCH(" ",K2)-1),Q1:R8,2,0)

where Q1:R8 is the lookup table (change range to suit).

 

[Sean15]

Does this work for you?

=VLOOKUP(LEFT(K2,FIND(" ",K2)-1),A:B,2,FALSE)

VLOOKUP(LEFT(K7,FIND(" ",K7)-1),Sheet2!A:B,2,FALSE) is returning #NA

JoeMoe mentioned names should be followed by a space but some names have spaces in them, Example:

the home depot 9526
topgolf san antonio 01
suppliesoutlet.com

These would be listed in the array table as:

the home depot
topgolf san Antonio
suppliesoutlet.com

Regards,

Sean

 

[Sean15]

Any suggestions anyone?

Sean

 

[Andrew Poulsom]

If your lookup table is sorted ascending by column A:

=VLOOKUP(K2,A:B,2)

 

[Sean15]

Thank you. Here is what I ended up:

VLOOKUP values in column K remains unchanged

I set up lookup table with first five characters of names in column k

All duplicates were removed from lookup table.

VLOOKUP formula will now be =IFERROR(VLOOKUP(LEFT(K7,5),Sheet2!$A$2:$B$1000,2,0),"name not found")

It's a very, very crude way of doing it but now I get my assigned codes.

Thank very much, as always, for your help and suggestions.

Sean