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

Solution 1:

=COUNTIF(some_names,D1)

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

Solution 2:

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)

Solution 3:

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)

Solution 4:

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!

Solution 5:

For variety you can use MATCH, e.g.

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