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

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
606
Office Version
  1. 2010
Platform
  1. Windows
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:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 25, 2005
Messages
606
Office Version
  1. 2010
Platform
  1. Windows
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:

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
606
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Any suggestions anyone?

Sean
 

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
606
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,734
Members
414,170
Latest member
Mdm

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top