Home > Blockchain >  Return YES or NOT if text from a list is in this cell
Return YES or NOT if text from a list is in this cell

Time:08-31

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:

  1. 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.
  2. Next, we'll use the ISNUMBER Function to check for a number. It returns TRUE for a number and FALSE if not.
  3. 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")

  • Related