Excel Index Match with text string

Question

enter image description hereI am trying to write a formula that will allow me to return a value based on it being matched with a short text string that is contained in a longer text string. Please see below. I would like the "Unit Time" column in the second table to retrieve that corresponding value from the first table by matching the shorter text string in the product column of the first table in the longer product name in the second table. Any thoughts would be appreciated.

Thanks

excel

Is it always trying to match the first two codes, or can the code be anywhere in the string? — Scott Craner
If you need only the first five characters to match (i.e. PD-DP) you can =VLOOKUP(LEFT(D2,5),...) — ttaaoossuuuu
I think that I would prefer to be able to do it assuming the text could be anywhere in the string — crhaag

B540Glenn B540Glenn · Accepted Answer · 2016-07-25T14:17:25

If the product will always be XX-XX then you can use:

=INDEX(B:B,MATCH(Left(D2,5),A:A,0))

If the product can be more than 2 characters around the dash, then you'll have to use the Find() formula to get the position of the end of the product in col D instead of Left().

To account for row 4 as Scott pointed out:

=INDEX(B:B,MATCH(LEFT(D2,FIND("-",D2,4)-1),A:A,0))

This will find the second dash in the string and use the variable length for the Left() function.

Excel Index Match with text string

1 Answers