I have the following columns on a spreadsheet

2 apple    3    
3 banana   6      TRUE
4 fish     9    
5 dog       
6 cherry          TRUE
7 goat      
8 horse     
9 chicken         TRUE

The LOCATION column is a list of row numbers pertaining to ITEM.

I'd like the EXISTS column to be marked as TRUE for the row number listed in the LOCATION column.

I'm really stuck and would appreciate some guidance.

  • Can you show what you have tried please? It will help to see where you've gotten stuck. – Dave Sep 7 '15 at 11:43
  • Your question is pretty ambiguous about the Exists column. Exactly what are you evaluating and where to determine whether it's true? – fixer1234 Sep 7 '15 at 21:36
  • Yes I'm sorry about that, I was really struggling to get my head around the problem so articulating it into a logical question was really tough. – ThatDude Sep 8 '15 at 7:01

You can do:


To always return TRUE or FALSE, or


To only return when TRUE and leave blank when FALSE.

How it works:

ROW() is what returns the current row number.

COUNTIF counts how many times the current row number is entered in column B (the LOCATION).

>0 checks if the result of the COUNTIF is more than 0 (row number is listed in the location column at least once) and returns TRUE or FALSE. Or you nest your Logical Test in an IF function where you can select what the TRUE or FALSE results should be.

  • Thank you, that gave me all the information I needed to solve my real world problem. – ThatDude Sep 8 '15 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.