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?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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.
 

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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.
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457

ADVERTISEMENT

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.
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

You can use VLOOKUP with a wildcard, e.g

=VLOOKUP("*"&A1&"*",B1:C10,2,0)
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,045
Messages
5,599,488
Members
414,313
Latest member
Oonagh123

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