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.

"KCK" wrote:

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!