In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list.

I've named the range A3:A10 'some_names', and I'd like an excel formula that will give me True/False or 1/0 depending on the contents.

=COUNTIF(some_names,D1)

should work (1 if the name is present - more if more than one instance).

• how can I modify the formula so that it works in the case where the `some_names` contains 2 columns, and also instead of D1 I have D1:E1? – user1993 Sep 18 '17 at 20:59

My preferred answer (modified from Ian's) is:

``````=COUNTIF(some_names,D1)>0
``````

which returns TRUE if D1 is found in the range some_names at least once, or FALSE otherwise.

(COUNTIF returns an integer of how many times the criterion is found in the range)

I know the OP specifically stated that the list came from a range of cells, but others might stumble upon this while looking for a specific range of values.

You can also look up on specific values, rather than a range using the `MATCH` function. This will give you the number where this matches (in this case, the second spot, so 2). It will return #N/A if there is no match.

``````=MATCH(4,{2,4,6,8},0)
``````

You could also replace the first four with a cell. Put a 4 in cell A1 and type this into any other cell.

``````=MATCH(A1,{2,4,6,8},0)
``````
• Very nice. Don't forget to add "quotes" if your value isn't a number (took me a couple tries to sort that out). – dav Nov 23 '15 at 21:34
• Sadly you can't use this in conditional formatting :( – StarWeaver Apr 18 '16 at 18:59
• Sure you can. With Excel 2007 and later, you can use the IFERROR function. =IFERROR(MATCH(A1,{2,4,6,8},0),0) Then, you can do your conditional formatting on whether that cell =0 or >0, whichever you prefer. – RPh_Coder Sep 7 '16 at 17:30
• `=OR(4={2,4,6,8})` – Slai Dec 8 '16 at 12:42
• This answer is clear that the solution returns #N/A - that is true. But is seems unhelpful: you can't use #N/A in an if clause, so you can't say IF(MATCH(4{2,3},0), "yay", "boo")... the answer is #N/A not "boo" – GreenAsJade Aug 2 '18 at 4:05

If you want to turn the countif into some other output (like boolean) you could also do:

=IF(COUNTIF(some_names,D1)>0, TRUE, FALSE)

Enjoy!

• The TRUE and FALSE help indicate what needs to be replaced – Darcys22 Mar 31 '17 at 0:48

For variety you can use MATCH, e.g.

`=ISNUMBER(MATCH(D1,A3:A10,0))`

there is a nifty little trick returning Boolean in case range `some_names` could be specified explicitly such in `"purple","red","blue","green","orange"`:

``````=OR("Red"={"purple","red","blue","green","orange"})
``````

Note this is NOT an array formula

• Actually, it is an array formula. What it's not is an array entered formula ;-) – robinCTS Jul 12 '18 at 1:50

You can nest `--([range]=[cell])` in an `IF`, `SUMIFS`, or `COUNTIFS` argument. For example, `IF(--(\$N\$2:\$N\$23=D2),"in the list!","not in the list")`. I believe this might use memory more efficiently.

Alternatively, you can wrap an `ISERROR` around a `VLOOKUP`, all wrapped around an `IF` statement. Like, `IF( ISERROR ( VLOOKUP() ) , "not in the list" , "in the list!" )`.

Array Formula version (enter with Ctrl + Shift + Enter):

``````=OR(A3:A10=D1)
``````
• This works. I think it got a downvote because the downvoter did not know how to enter an array formula... it should look like {=OR(R34:R36=T34)} after it's entered, if you entered it corretly – GreenAsJade Aug 2 '18 at 7:15

In situations like this, I only want to be alerted to possible errors, so I would solve the situation this way ...

``````=if(countif(some_names,D1)>0,"","MISSING")
``````

Then I'd copy this formula from `E1` to `E100`. If a value in the `D` column is not in the list, I'll get the message MISSING but if the value exists, I get an empty cell. That makes the missing values stand out much more.