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
up vote 65 down vote accepted

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)
  • 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 :( – 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. – George Dooling Sep 7 '16 at 17:30
  • 5
    =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 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!

  • 17
    IF( ,TRUE,FALSE) is redundant. – pnuts May 29 '13 at 23:37
  • 2
    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 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!" ).

  • Do you have further details of more efficient memory usage (eg space or speed)? – pnuts Aug 6 '15 at 20:29

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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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