How to check if a cell contains a value from a column in another table

Question

Level 1

4 points

How to check if a cell contains a value from a column in another table

Hi Folks.

In Numbers I'm trying to find if a value I've entered into a cell also appears in a column in another table.

Let's say cell D3 has the word "APPLE" in it. I want to see if APPLE appears in a column of words I have in a separate table (lets call the table "Foods my daughter won't eat".

The result I want is a TRUE/FALSE answer, then based on that I can do some other manipulation.

I had figured that this would be easy but it appears not to be.

Any advice would be greatly appreciated.

Cheers

Jay

Posted on Aug 2, 2023 1:59 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,580 points

Aug 2, 2023 8:18 AM in response to jepps77

You can get the TRUE/FALSE with COUNTIF, something like this:

=COUNTIF(No eat::$A,D3)>0

Further manipulation could be something like this:

=IF(E3,"Won't eat","Will eat")

Or, combine the two steps into something like this:

=IF(COUNTIF(No eat::$A,D3)>0,"Won't eat","Will eat")

Use ; instead of , in the formulas if your region uses , as the decimal separator.

SG

Reply

Answer 2

jepps77 Author

Level 1

4 points

Aug 2, 2023 8:38 AM in response to SGIII

Thanks a lot for that.

I’m not sure how I missed it but I had my arguments back to front.

When I was using COUNTIF it would only give an error - but after reviewing your answer I could see my mistake.

Thanks again.

Jay

Reply

Answer 3

SGIII

Level 8

37,580 points

Aug 2, 2023 9:06 AM in response to jepps77

Nice to hear you got it sorted. Thanks for the green tick!

SG

Reply