V-LOOKUP If The Reference Data only Partially Matches

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
I'm new too the board, but thought I'd try my luck. I'm trying too use the V-Look Up Feature and too pull data from one spreadsheet to another. However, the source column in the spreadsheet I'd like too pull data from not only has the matching word/characters but other data as well separated by spaces. Is there a way for V-Look Up to do this, or is there another feature in Excel?? I've been trying to resolve this issue for years, I normally end up doing it manually. Can anyone help?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi John and Welcome to the Board!

Could you please include some sample data? I think that you have a value like
Code:
  V183 snoozer
in cell A20 and you want to match it against a table that looks like this:

Code:
  A      B
  B156 Jetski
  V183 Snowmobile
  P224 Apple
and you'd like to get the result of "Snowmobile" because your value starts with "V183 " but the "snoozer" is interfering.

You can do a string manipulation formula to isolate the first "word" in the cell and then use the VLOOKUP on that.
 
Upvote 0
Are you wanting to just look up the first word?

If so, something like this may do it;

Code:
=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),LOOKUPRANGE,CELLREF,FALSE)

HTH
 
Upvote 0
Try
Code:
=VLOOKUP(LEFT(A20, FIND(" ", A20) - 1), etc.
instead of trying the VLOOKUP directly on A20.

And if I have misinterpreted your problem then including the sample data will really help.
 
Upvote 0
VLOOK UP

Hi John and Welcome to the Board!

Could you please include some sample data? I think that you have a value like
Code:
  V183 snoozer
in cell A20 and you want to match it against a table that looks like this:

Code:
  A      B
  B156 Jetski
  V183 Snowmobile
  P224 Apple
and you'd like to get the result of "Snowmobile" because your value starts with "V183 " but the "snoozer" is interfering.

You can do a string manipulation formula to isolate the first "word" in the cell and then use the VLOOKUP on that.



"No, but using your sample, Dan it's more like this


A B
B156 Jetski
V183 V184 Snoozer
P224 Apple

I'm trying too pull Snoozer, But in Column A (the source file) I have V183 and also V184. Not sure but can this be done with VLook Up?

Thanks for your help.
 
Upvote 0
It can be done with the data in this format but it would require complex formulas. VLOOKUP would work much more straightforwardly if your lookup table data were in the following format:
Code:
A        B
B156  Jetski
V183  Snoozer
V184  Snoozer
P224  Apple
Then V183 and V184 would each match to Snoozer.
 
Upvote 0
Are you wanting to just look up the first word?

If so, something like this may do it;

Code:
=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),LOOKUPRANGE,CELLREF,FALSE)

HTH

"Thanks for your help. This is what I have =VLOOKUP(A2,'[LARRY RICOH VLOOK UP TEST.xls]RICOH'!$A$2:$B$30000,2,FALSE)

I'm trying to pull data from Column B of the LOOKUPRANGE, but column A of the LOOKUPRANGE has more than the word/numbers: Say for instance A2 in the reference is "124", but in the LOOKUPRANGE Column A cells might have "236 799 124 355". Is it possible?
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top