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



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

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


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.


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

  • 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 :(
    – Weaver
    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
  • 9
    – 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

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)


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

there is a nifty little trick returning Boolean in case range some_names could be specified explicitly such in "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, 2018 at 1:50

For variety you can use MATCH, e.g.



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


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


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.


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

  • 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .