104

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.

Improve this question

9 Answers 9

Reset to default
103

=COUNTIF(some_names,D1)

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

Improve this answer
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?
    – user1995
    Commented Sep 18, 2017 at 20:59
79

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)

Improve this answer
0
41

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)
Improve this answer
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
    Commented Nov 23, 2015 at 21:34
  • 1
    Sadly you can't use this in conditional formatting :(
    – Weaver
    Commented 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
    Commented Sep 7, 2016 at 17:30
  • 14
    =OR(4={2,4,6,8})
    – Slai
    Commented 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"
    – GreenAsJade
    Commented 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!

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

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

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

For variety you can use MATCH, e.g.

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

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

Improve this answer
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.

Improve this answer
0
0

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

=OR(A3:A10=D1)
Improve this answer
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
    – GreenAsJade
    Commented 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 .