VLOOKUP problem

[Jason]

Hi,

I am struggling to find a solution to match my requirements & would
appreciate it if somebody can help me out. I have a spreadsheet with just
under 4500 lines of information. For each line, there are around 10 entries
(i.e.... 'description', 'shipment number', 'order number', 'cost per item',
'supplier', 'shipment method' etc).

I have a second spreadsheet which contains a completely different set of
information with the only common reference between the two spreadsheets
being the 'order number'.

I am looking to do a 'VLOOKUP' type search in my 2nd spreadsheet, to
reference the 'order number' in the 1st spreadsheet & return the result
from the shipment method column.

My problem: Spreadsheet 1 contains all of the information I require, but
the cells in the 'order number' column contain more than one reference all
within a single cell (i.e.... "PO1234, PO1235, PO1237"). So, if I do a
VLOOKUP and the lookup value of the cell I wish to lookup is "PO1235", it
will not be found because there is no cell in my 2nd spreadsheet with
just"PO1235" in it. It will be in a cell with "PO1234, PO1235, PO1237", in
it. I need VLOOKUP to find my cell reference within a cell within and then
return a value from a corresponding line (shipment method).

Thanks, Jason

 

[Guest]

One way

=INDEX(Sheet3!$B$2:$B$4000,MATCH(TRUE,ISNUMBER(FIND("PO1234",Sheet3!$G$2:$G$4000)),0))

entered with ctrl + shift & enter
where Sheet3!G2:G4000 is the range that holds the shipment method
and Sheet3!B2:B4000 the range that holds the shipment number


Regards,

Peo Sjoblom

 

[Dave Peterson]

Another option:

=VLOOKUP("*"&A1&"*",sheet2!a1:b999,2,FALSE)