I am using index-match to check in a table for multiple values. Now my problem is that I have multiple values in one cell.
My sheet looks like this:
The first formula looks like this:
=INDEX(C2:C11;MATCH(F2&G2;INDEX($A$2:$A$11&$B$2:$B$11;0);-1))
As you can see, it only works for HIGH, but when it comes to LOW it is wrong.(is marked in red in the above image)
Any suggestions, how to fix that?
You are going to have to pull some type of wildcard match to find a string-in-a-string rather than the appromate MATCH function lookup using a -1 as the optional [match_type]¹.
The array formula² in H2 is,
=INDEX($C$2:$C$11; MATCH("*"&G2&"*"; IF($A$2:$A$11=F2; $B$2:$B$11); 0))
That formula presupposes that you are not going to get false positives on your sector data by having a sector like KL or TT that would be found within PKLP or TTL. If your sectors could produce false positives like that then you will to add the <comma><space> delimiter. In this case the array formula² would be,
=INDEX($C$2:$C$11; MATCH("*, "&G2&", *"; IF($A$2:$A$11=F2; ", "&$B$2:$B$11&", "); 0))
Note that the <comma><space> delimiters have been added to both the source and the target.
¹ A [match_type] of 1 or -1 in a MATCH function should never be used on unsorted data. Correct results are more coincidence than reliable when used on unsorted data.
²Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.
;. Would you be so kind to edit your answer! Thx in advance!