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

#### Sean15

##### Well-known Member
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

Last edited:

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Does this work for you?

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

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

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

Last edited:
Any suggestions anyone?

Sean

If your lookup table is sorted ascending by column A:

=VLOOKUP(K2,A:B,2)

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.

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

Replies
8
Views
87
Replies
3
Views
134
Replies
2
Views
140
Replies
3
Views
483
Replies
2
Views
576

1,203,072
Messages
6,053,377
Members
444,660
Latest member
Mingalsbe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back