Excel Formula INDEX MATCH multiple search criteria

Question

I'm trying to find a way to do an INDEX MATCH on multiple criteria but I am not having much luck.

I have 3 text entries that I want to flag the "Harmful" tag, which are "c001","c002" and "c003".. they will likely not be in a sorted list.

=IFERROR(IF(INDEX(F9:F34,MATCH("*C001*",$B$9:$B$34,0)),"Harmful",""),"")

Now this above works perfectly but every combination with nested IF statements and IF(OR formulae don't work for me!

Note that I am using wildcards because these codes are likely to form part of a longer text string.

Any advice/guidance will be greatly appreciated.

Kind Regards.

Tom Sharpe · Accepted Answer · 2016-10-18 08:20:06Z

1

If you did want to do it in one formula, try this one:-

=IF(SUMPRODUCT(ISNUMBER(FIND({"C001","C002","C003"},B9:B34))*ISNUMBER(F9:F34)),"Harmful","")

Actually this doesn't quite do the same as OP's INDEX/MATCH because a zero in F9:F34 in the above formula would give ISNUMBER=TRUE and could flag it as harmful while a zero in the original formula wouldn't.

Alternative:-

=IF(SUMPRODUCT(ISNUMBER(FIND({"C001","C002","C003"},B9:B34))*N(+F9:F34)),"Harmful","")

I was going to add that the reason nested IF's and OR's don't work with OP's formula is that when the match fails for the first time the formula's execution goes straight to the empty string "" in the IFERROR statement so it doesn't evaluate any other conditions. You'd have to separate them something like this:-

=IF(IFERROR(INDEX(F9:F34,MATCH("*C001*",$B$9:$B$34,0)),0)+IFERROR(INDEX(F9:F34,MATCH("*C002*",$B$9:$B$34,0)),0)+IFERROR(INDEX(F9:F34,MATCH("*C003*",$B$9:$B$34,0)),0),"Harmful","")

edited Oct 18, 2016 at 8:20

answered Oct 17, 2016 at 14:10

Tom Sharpe

35.2k5 gold badges29 silver badges47 bronze badges

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

3 Comments

vividillusion Over a year ago

This works very well however it does not work as I intend, I was using the MATCH function because all 3 of those are row headers and I only want it to flag as harmful if there is a value in one of these rows. If that makes sense.

Tom Sharpe Over a year ago

I wasn't quite sure what you meant, do you mean you only want it to say 'Harmful" if one of the strings is found, not two or three? Might need an example to explain it to me.

Tom Sharpe Over a year ago

Or did you want to pair column F with B, then G with C or something like that?

Dave P · Accepted Answer · 2016-10-17 14:08:56Z

Is there a particular reason it needs to be in the form of an INDEX function, or need to all be done in one column? (If it is, I'll hazard a guess in your formula you want the INDEX function to be looking at the same column B that's in your MATCH function?... maybe one reason it's not working?)

A simpler solution imho would be to add some new columns next to your list, which check for the existence of each of your search strings, and record a flag of 1, which asserts existence. The FIND function is probably what you're after, which returns an error if it cannot find the reference string.

i.e.

in cell H9 =IF(ISERROR(FIND("C001",F$9)),1,0)

in cell I9 =IF(ISERROR(FIND("C002",F$9)),1,0)

... etc

Then at the end of these check columns, sum up the values. You will then know if the sum is greater than zero then at least on of your bad strings has been found, and that is a harmful value to flag.

Collectives™ on Stack Overflow

Excel Formula INDEX MATCH multiple search criteria

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related