• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

checking multiple values in VLOOKUP not equal

In order to check Anot equal to "Red" in a Vlookup statement, I would code   A,"<>Red".

Is there a way to check A not equal to "Red", "Green" or "Blue" in vlookup without repeating the criteria similar to A not in ("Red", "Green", "Blue")

The reason is that I am comparing the value to a value returned from a vlookup, so I did not want to have concatanated vlookups if possible
2 Solutions
I have taken to using worksheets to perform lookups and then referencing those in formula to keep the code cleaner.

You could also create vb function to perform the lookup and return the result.

You can use this
If your VLOOKUP is VLOOKUP(E5,$A$1:$B$5,2,FALSE)
You can use this expression


It returns True if the vlookup returns a value that is none of Red,Green,Blue

=IF(ISNA(MATCH(VLOOKUP(E5,$A$1:$B$5,2,FALSE),{"Red","Blue","Green"},0)),"no red green or blue", "was matched")
You could also test a SUMPRODUCT formula. Using cyberkiwi's example:
=IF(SUMPRODUCT((A1:A5=E5)*(B1:B5={"red","green","blue"})),"was matched","no red, green or blue")

The SUMPRODUCT formula is a little shorter and only needs two levels of nesting (vs four for IF(ISNA(MATCH(VLOOKUP...)

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now