Pretty straightforward but couldnt do it.
I have a list of words on A:A.
I am trying to make a function that will tell you whether or not the word you write in a specific cell is on that list.
So you have the cell B5, and you type "PURPLE". If the word "PURPLE" is on the list (A:A) it will return "Yes" on B6, otherwise it will return "No".
Thank you for your help!
CodePudding user response:
try:
=IF(IFERROR(MATCH(B5; A:A; 0); FALSE)=TRUE; "yes"; "no")
CodePudding user response:
- First, use the MATCH Function to lookup the word in the list. If it finds a match, it will return the relative position, which is a number. If it fails to find a match, it will return an error.
- Next, we'll use the ISNUMBER Function to check for a number. It returns TRUE for a number and FALSE if not.
- Finally, we use the IF Function to check if the result of the ISNUMBER is TRUE, which means there's a match. We return "Yes" for TRUE and "No" for FALSE.
Here's the final formula:
=IF(ISNUMBER(MATCH(B5,A:A,0)),"Yes","No")