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

Question

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.

pnuts · Answer 1 · 2015-08-08T19:07:37.550

100

=COUNTIF(some_names,D1)

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

edited Aug 08 '15 at 19:07

answered May 29 '13 at 20:47

pnuts

6,142

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? – user1995 Sep 18 '17 at 20:59

score 75 · Accepted Answer · edited Aug 06 '15 at 20:26

75

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)

edited Aug 06 '15 at 20:26

pnuts

6,142

answered Jun 06 '13 at 20:40

joseph.hainline

2,142

score 37 · Answer 3 · edited Apr 03 '17 at 19:17

37

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)

edited Apr 03 '17 at 19:17

CJBS

171

answered Nov 10 '14 at 22:57

RPh_Coder

498

1

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
1

Sadly you can't use this in conditional formatting :( – Weaver 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 07 '16 at 17:30
12

=OR(4={2,4,6,8}) – Slai Dec 08 '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 02 '18 at 04:05
That just needs a level of error checking. This will return a 0 if you get N/A, then you can do your normal if statement. =IF(IFNA(MATCH(4,{2,4,6,8},0), 0), "yay", "boo"). Or you use @Slai's suggestion and don't use MATCH. – RPh_Coder Aug 03 '18 at 05:53

score 17 · Answer 4 · answered May 29 '13 at 21:09

17

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!

answered May 29 '13 at 21:09

Ian McClellan

552

3

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

score 12 · Answer 5 · answered Jul 11 '18 at 22:06

12

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

answered Jul 11 '18 at 22:06

gregV

234

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

score 9 · Answer 6 · answered May 29 '13 at 23:28

9

For variety you can use MATCH, e.g.

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

answered May 29 '13 at 23:28

barry houdini

10,992

score 2 · Answer 7 · answered Dec 05 '13 at 19:33

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!" ).

Slai · Answer 8 · 2017-01-05T15:50:14.197

0

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

=OR(A3:A10=D1)

edited Jan 05 '17 at 15:50

answered Dec 08 '16 at 12:38

Slai

119

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 02 '18 at 07:15

score 0 · Answer 9 · edited Jan 09 '14 at 18:37

0

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.

edited Jan 09 '14 at 18:37

Dan Atkinson

597

answered Aug 24 '13 at 11:59

Catchoval

11

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

9 Answers9

Linked