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.
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>
The second table has the following...
<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
Table I need to pull data into.
Order | Line | Cust. ID | Drop Ship | Ship Via | PO |
1362746 | 1 | GRECOA2 | TRUE | BEST | 229213 |
1362904 | 1 | GRECOA6 | TRUE | BEST | 229212 |
1326311 | 1 | GROGREMI | TRUE | OUR | 229365 |
1364101 | 1 | URBGARW | TRUE | BEST | 229356 |
1364143 | 1 | ALTGAR | TRUE | BEST | 229362 |
1364147 | 1 | WAYTO | TRUE | BEST | 229366 |
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>
The second table has the following...
Invoice | Invoice Date | Description | |||
6037880051 | 5/10/16 | 0 | 1216527/1214769 | ||
6037936603 | 5/4/16 | 0 | 232664H/232679H | ||
6038303452 | 1/5/16 | 0 | 228903H/227992H | ||
6038391094 | 3/23/16 | 0 | 233795H/233804H/233805H | ||
6038411601 | 4/16/16 | 0 | 228905H/233797H | ||
6038554569 | 2/8/16 | 0 | 233812H/233798H | ||
6038686776 | 1/31/16 | 0 | TFO002656/TFO002549 | ||
6038892314 | 4/20/16 | 0 | 233636/231536 | ||
6039207553 | 5/10/16 | 0 | 234729H/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