CODEWITHSUNDEEP
excel-formula
D G
D G

Reputation: 9

Partial match Excel

I have Table A which contains only strings, or strings+numbers, in general the list is very inconsistent. I also have Table B which contains a list of the names I have to retrieve from Table A where there is a match or partial match. My issue is that sometimes my formula doesn't work.

As you can see in Column C the below formula works for all except for rows 5 and 8 because I want to retrieve "toro res" which is in my list (table B) and instead I have 0.

enter image description here

Upvotes: 1

Views: 83

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

As I stated create a table with inputs in one column and output in another, then change the return of the formula to the output column:

=INDEX($I$2:$I$5,MATCH(1,COUNTIF(A2,"*" & $H$2:$H$5 & "*"),0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 1

Related Questions