• Status: Solved
• Priority: Medium
• Security: Public
• Views: 455

# 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
0
morinia
2 Solutions

Commented:
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.

0

Commented:
You can use this
You can use this expression

ISNA(MATCH(VLOOKUP(E5,\$A\$1:\$B\$5,2,FALSE),{"Red","Blue","Green"},0))

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

=IF(ISNA(MATCH(VLOOKUP(E5,\$A\$1:\$B\$5,2,FALSE),{"Red","Blue","Green"},0)),"no red green or blue", "was matched")
0

Commented:
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...)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.