How to Search/Find within cell to vlookup/Match & Index

[ibesmond]

I'm trying to figure out a formula to replace a simple Vlookup formula because my data has mixed references.

Table I need to pull data into.

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Order[/TD]
[TD="width: 64"]Line[/TD]
[TD="width: 64"]Cust. ID[/TD]
[TD="width: 64"]Drop Ship[/TD]
[TD="width: 64"]Ship Via[/TD]
[TD="width: 64"]PO[/TD]
[/TR]
[TR]
[TD="align: right"]1362746[/TD]
[TD="align: right"]1[/TD]
[TD]GRECOA2[/TD]
[TD="align: center"]TRUE[/TD]
[TD]BEST[/TD]
[TD="align: right"]229213[/TD]
[/TR]
[TR]
[TD="align: right"]1362904[/TD]
[TD="align: right"]1[/TD]
[TD]GRECOA6[/TD]
[TD="align: center"]TRUE[/TD]
[TD]BEST[/TD]
[TD="align: right"]229212[/TD]
[/TR]
[TR]
[TD="align: right"]1326311[/TD]
[TD="align: right"]1[/TD]
[TD]GROGREMI[/TD]
[TD="align: center"]TRUE[/TD]
[TD]OUR[/TD]
[TD="align: right"]229365[/TD]
[/TR]
[TR]
[TD="align: right"]1364101[/TD]
[TD="align: right"]1[/TD]
[TD]URBGARW[/TD]
[TD="align: center"]TRUE[/TD]
[TD]BEST[/TD]
[TD="align: right"]229356[/TD]
[/TR]
[TR]
[TD="align: right"]1364143[/TD]
[TD="align: right"]1[/TD]
[TD]ALTGAR[/TD]
[TD="align: center"]TRUE[/TD]
[TD]BEST[/TD]
[TD="align: right"]229362[/TD]
[/TR]
[TR]
[TD="align: right"]1364147[/TD]
[TD="align: right"]1[/TD]
[TD]WAYTO[/TD]
[TD="align: center"]TRUE[/TD]
[TD]BEST[/TD]
[TD="align: right"]229366[/TD]
[/TR]
</tbody>[/TABLE]


The second table has the following...

[TABLE="width: 397"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Invoice[/TD]
[TD]Invoice Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD="align: right"]6037880051[/TD]
[TD="align: right"]5/10/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]1216527/1214769[/TD]
[/TR]
[TR]
[TD="align: right"]6037936603[/TD]
[TD="align: right"]5/4/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]232664H/232679H[/TD]
[/TR]
[TR]
[TD="align: right"]6038303452[/TD]
[TD="align: right"]1/5/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]228903H/227992H[/TD]
[/TR]
[TR]
[TD="align: right"]6038391094[/TD]
[TD="align: right"]3/23/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]233795H/233804H/233805H[/TD]
[/TR]
[TR]
[TD="align: right"]6038411601[/TD]
[TD="align: right"]4/16/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]228905H/233797H[/TD]
[/TR]
[TR]
[TD="align: right"]6038554569[/TD]
[TD="align: right"]2/8/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]233812H/233798H[/TD]
[/TR]
[TR]
[TD="align: right"]6038686776[/TD]
[TD="align: right"]1/31/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]TFO002656/TFO002549[/TD]
[/TR]
[TR]
[TD="align: right"]6038892314[/TD]
[TD="align: right"]4/20/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]233636/231536[/TD]
[/TR]
[TR]
[TD="align: right"]6039207553[/TD]
[TD="align: right"]5/10/16[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]234729H/234728H DROP SHIP[/TD]
[/TR]
</tbody>[/TABLE]

So I'm trying to look up the PO number but the second table contains lot of information. Sales orders which are 7 numerals, POs which are 6 and POs with an "H" suffix, transfers which are 9 and more.

I have tried to use left, mid and right formulas to edit the Description field, and replace to remove the "H" and then run vlookups against each column, but sometimes a description field can have as many as 6 POs, which means I have to create 6 columns, separate all the POs and then vlookup against each column.

I wasn't sure if there is a way to search for a string in a field and then do the vlookup or index/match against that.

Please let me know if you have any ideas.

Thank you

 

[sheetspread]

If the numbers don't repeat, something like:

Excel 2010
	A	B	C	D	E	F
1	Invoice	Invoice Date	Description
2	6037880051	5/10/2016	1216527/1214769
3	6037936603	5/4/2016	232664H/232679H
4	6038303452	1/5/2016	228903H/227992H
5	6038391094	3/23/2016	233795H/233804H/233805H
6	6038411601	4/16/2016	228905H/233797H
7	6038554569	2/8/2016	233812H/233798H
8	6038686776	1/31/2016	TFO002656/TFO002549
9	6038892314	4/20/2016	233636/231536
10	6039207553	5/10/2016	234729H/234728H DROP SHIP
11
12
13					233804H	3/23/2016
14					231536	4/20/2016
Sheet14

Cell Formulas
Range	Formula
F13	=INDEX($B$1:$B$10,SUMPRODUCT(--(ISNUMBER(SEARCH(E13,$C$2:$C$10))),ROW($C$2:$C$10)))

=INDEX($B$2:$B$10,MATCH(TRUE,ISNUMBER(SEARCH(E13,$C$2:$C$10)),0)) (ctrl-shift-enter) works too