Vlookup to find a match in a string


K

KCK

Hello, I am trying to use vlookup to match the contents of a cell with a
portion of a text string and return another cells data. Maybe using Index and
Match would be better but I can't figure out how to accomplish this wtih
either of them.
For example: Sheet1 Column A contains a list of Sales Oder numbers, one
Sales Order per cell.
Sheet2 Column C contains several Sales Order numbers in each cell. Using
the Sales Order number in Sheet1 column A I need to find that Sales Order
number in Sheet2 Column C and return Column D to Sheet 1. Any help is
greatly appreciated!
 
Ad

Advertisements

E

Eduardo

Hi,
In sheet1 column B enter
=Vlookup(A1,sheet2!$C:$D,2,false), if not using excel 2007 use
=Vlookup(A1,sheet2!$C1:$D1000,2,false),
 
S

Shane Devenshire

Hi,

Here is one array solution, you will need to add the sheet references:

=INDEX(D1:D10,MAX(ROW(C1:C10)*ISNUMBER(SEARCH(A1,C1:C10))),0)

To make it an array press Shift+Ctrl+Enter to enter it.
 
S

Sheeloo

Try this (change 100 to your last row on Sheet1)

=VLOOKUP(LOOKUP(2,1/IF(FIND(A1,Sheet2!$C$1:$C$100)>0,ROW(),0),Sheet2!$C$1:$C$100),Sheet2!C:D,2,FALSE)

You will have to use CTRL-SHIFT-ENTER instead on normal ENTER after
typing/pasting the formula in B1... You can then copy the formula down...

Note that you will get the LAST match if there are more than one rows with
matching Sales Order in Sheet2!C... and #N/A if there is no match.

Let us know how it goes.
 
T

T. Valko

It would have been a good idea to show us some samples.

Here's a generic array formula** that should get you headed in the right
direction:

=INDEX(Sheet2!D1:D5,MATCH(TRUE,ISNUMBER(SEARCH(A1,Sheet2!C1:C5)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
S

Sheeloo

Eduardo,

KCK want to do the lookup when A1 is part of the string in a cell in
Sheet2!C...

Also Excel 2003 works with
=Vlookup(A1,sheet2!C:D,2,false)

Absolute reference is not required since you are using the whole column...
 
Ad

Advertisements

K

KCK

Thanks to everyone, your input was great. I got Shane Devonshires to work
perfectly! I really appreciate all your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top