Can't "Index/Match" where multiple values held in one cell

[Ochimus]

As shown on Rows 1 - 9 on the attached, Index/Matching is straightforward where each cell holds only one value.

But need the formula that can find a match when there are multiple values in a cell as shown in B14?

All solutions, suggestions and alternatives accepted gratefully, as ever

Ochimus

[Glenn Kennedy]

is thiswhat you mean?


=IF(ISNUMBER(SEARCH(B17,B14)),C14,"")

[Ochimus]

Glenn, Many thanks for prompt response.

Your solution is perfect for what I attached, but just seen End User has several rows of cells with multiples, each of which have different values (e.g. B14 - B16 each have at least five codes each, with different prices in C14:C16

So I'm looking to search in B14:B16 for whichever code is in B21, and find the matching price in C14:C16

Hope you can solve that extension as quickly?

Ochimus

[Glenn Kennedy]

Yep.

=INDEX($C$14:$C$16,MATCH("*"&B19&"*",$B$14:$B$16,0))

[azumi]

Not sure try put this on C17:
=SUMPRODUCT(SUMIF($B$2:$B$6,TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($B17,"."," "))&" "," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(TRIM(SUBSTITUTE($B17,"."," "))&" ")-LEN(SUBSTITUTE(TRIM(SUBSTITUTE($B17,"."," "))&" "," ",""))))*99-99+1,99)),$C$2:$C$6))

[Ochimus]

Glenn,

Many thanks for the solution to the expanded challenge.


Can't remember seeing wild cards in Index Matches before, but proves Old Dogs can learn New Tricks!

Ochimus