Hi,

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

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

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


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