Vlookup to find a match in a string

[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!

 

[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),

 

[Shane Devenshire]

Hi,

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

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

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

 

[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,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. 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!D15,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.

 

[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,2,false)

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

 

[KCK]

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