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

ibesmond

New Member
Joined
Nov 26, 2010
Messages
17
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.

OrderLineCust. IDDrop ShipShip ViaPO
13627461GRECOA2TRUEBEST229213
13629041GRECOA6TRUEBEST229212
13263111GROGREMITRUEOUR229365
13641011URBGARWTRUEBEST229356
13641431ALTGARTRUEBEST229362
13641471WAYTOTRUEBEST229366

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>


The second table has the following...

InvoiceInvoice DateDescription
60378800515/10/1601216527/1214769
60379366035/4/160232664H/232679H
60383034521/5/160228903H/227992H
60383910943/23/160233795H/233804H/233805H
60384116014/16/160228905H/233797H
60385545692/8/160233812H/233798H
60386867761/31/160TFO002656/TFO002549
60388923144/20/160233636/231536
60392075535/10/160234729H/234728H DROP SHIP

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
If the numbers don't repeat, something like:


Excel 2010
ABCDEF
1InvoiceInvoice DateDescription
260378800515/10/20161216527/1214769
360379366035/4/2016232664H/232679H
460383034521/5/2016228903H/227992H
560383910943/23/2016233795H/233804H/233805H
660384116014/16/2016228905H/233797H
760385545692/8/2016233812H/233798H
860386867761/31/2016TFO002656/TFO002549
960388923144/20/2016233636/231536
1060392075535/10/2016234729H/234728H DROP SHIP
11
12
13233804H3/23/2016
142315364/20/2016
Sheet14
Cell Formulas
RangeFormula
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top