CODEWITHSUNDEEP
excel-formula
crhaag
crhaag

Reputation: 45

Excel Index Match with text string

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

Upvotes: 1

Views: 7819

Answers (1)

B540Glenn
B540Glenn

Reputation: 409

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.

Upvotes: 1

Related Questions