98

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.

9 Answers 9

98

=COUNTIF(some_names,D1)

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

1
  • 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, 2017 at 20:59
72

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)

0
34

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)
6
  • 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, 2015 at 21:34
  • 1
    Sadly you can't use this in conditional formatting :(
    – StarWeaver
    Apr 18, 2016 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, 2016 at 17:30
  • 8
    =OR(4={2,4,6,8})
    – Slai
    Dec 8, 2016 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" Aug 2, 2018 at 4:05
18

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!

1
  • 3
    The TRUE and FALSE help indicate what needs to be replaced
    – Darcys22
    Mar 31, 2017 at 0:48
9

For variety you can use MATCH, e.g.

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

8

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

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

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

0
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.

0
0

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

=OR(A3:A10=D1)
1
  • 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 Aug 2, 2018 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.