Using Index and Match to search an array that has multiple items in each cell

Hello,

I am using a combination of Index and Match to search an array on a separate worksheet called 'Stress Index'. The value I am searching for is a combination of letters and numbers (ex. HPF-20-AS-201) and am returning the corresponding value in column C of the array. This is my function:

=INDEX('Stress Index'!$C$10:$U$76,MATCH(T3,'Stress Index'!$U$10:$U$76,0),1)

It is working, however in the array I am searching some cells have more text than just the part I am searching for in them, and it is not working for those ones. To clarify please refer to the following picture:

Excel question.JPG

If I searched for 'FG-6-BL-201' It would return the value in Column C of row 39. However if I searched for 'LPF-8-AS-201' it will not find that value where it should in Row 44. It returns #N/A in this instance.

Is there a way to search within a cell to find the correct string? Or maybe make it so that the search value doesn't have to match exactly to get a result?

Thanks in advance for any help.

Change your formula to:

=INDEX('Stress Index'!$C$10:$U$76,MATCH("*" & T3 & "*",'Stress Index'!$U$10:$U$76,0),1)

And that will match any cell containing T3

Thank you that worked perfectly!

Hi.

Welcome to the forum.

Try putting an asterisk either side of your search string:

Please Login or Register  to view this content.

Cheers, Rob.

Damn! Beaten on the draw again. :P

@ tdlewis

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.