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
– Scott Craner, Commented Jul 25, 2016 at 14:12
If you need only the first five characters to match (i.e. PD-DP) you can =VLOOKUP(LEFT(D2,5),...) — Taosique
– Taosique, Commented Jul 25, 2016 at 14:12
I think that I would prefer to be able to do it assuming the text could be anywhere in the string — crhaag
– crhaag, Commented Jul 25, 2016 at 15:40

B540Glenn · Accepted Answer · 2016-07-25 14:38:41Z

1

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.

edited Jul 25, 2016 at 14:38

answered Jul 25, 2016 at 14:17

B540Glenn

4092 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scott Craner Over a year ago

Look at row 4, it does not follow that pattern.

Scott Craner Over a year ago

I like your second formula. I wish the OP would answer the questions. Mine was a little more involved. =INDEX(B:B,MATCH(LEFT(D2,FIND("}}}",SUBSTITUTE(D2,"-","}}}",2))-1),A:A,0)) or =VLOOKUP(LEFT(D2,FIND("}}}",SUBSTITUTE(D2,"-","}}}",2))-1),$A:$B,2,0) This does not care about the pattern it will find the first second - regardless of how many characters in the first two. But the question is what happens when the lookup in Column D matches perfectly? Will that ever happen? Too many questions and no feed back.

crhaag Over a year ago

This works. Thanks. I wonder though if there is a way to do it without basing it on the second dash and using the actual text. I can see situations in which I might need to go further than the second dash

Collectives™ on Stack Overflow

Excel Index Match with text string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related