In Excel, how do I check if a cell is in a list of values (a range of cells)
This video is brought to you by Memorabit, an app that sends technical terms, commands and definitions as reminders at unexpected moments, so they stay in memory. Built on cognitive science principles. Start free on web, iOS and Android:
iOS: https://apps.apple.com/app/id6782301691
Android: https://play.google.com/store/apps/detai...
--------------------------------------------------
Read the revolutionary AI books written by Turbina Editore, the first publishing house entirely run by artificial intelligence. This groundbreaking publisher has transformed the literary world, producing the most sought-after AI-generated books. Start reading today at:
https://turbinaeditore.com/en/
--------------------------------------------------
--------------------------------------------------
Hire the world's top talent on demand or became one of them at Toptal: https://topt.al/25cXVn
and get $2,000 discount on your first invoice
--------------------------------------------------
Take control of your privacy with Proton's trusted, Swiss-based, secure services.
Choose what you need and safeguard your digital life:
Mail: https://go.getproton.me/SH1CU
VPN: https://go.getproton.me/SH1DI
Password Manager: https://go.getproton.me/SH1DJ
Drive: https://go.getproton.me/SH1CT
Music by Eric Matyas
https://www.soundimage.org
Track title: Hypnotic Orient Looping
--
Chapters
00:00 In Excel, How Do I Check If A Cell Is In A List Of Values (A Range Of Cells)
00:27 Answer 1 Score 110
00:42 Answer 2 Score 18
00:57 Accepted Answer Score 85
01:19 Answer 4 Score 45
01:56 Thank you
--
Full question
https://superuser.com/questions/601419/i...
--
Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...
--
Tags
#microsoftexcel #worksheetfunction
#avk47
ANSWER 1
Score 110
=COUNTIF(some_names,D1)
should work (1 if the name is present - more if more than one instance).
ACCEPTED ANSWER
Score 85
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)
ANSWER 3
Score 45
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)
ANSWER 4
Score 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!