Would an IF statement or LOOKUP be better for this?

[Candide]

Hi all,

Trying to figure out how to flag whether data from Sheet1 B1 can be flagged in C1 whether it is a true statement from Sheet2. For example I have a table with customer's favorite number in Sheet1 and I am trying to flag in the C1 cell if it is a Fibonacci, prime and/or square number.

Can anyone help me? When I have used LOOKUP and IF statements it comes up with #SPILL!
Sheet 1 - table

Sheet 2 - not in a table. Should it be in a table?

 

[awoohaw]

In cell C2 put:

=SUM(--(B2=Sheet2!$a$2:$c$400))>0

you may need to enter with CNTL-SHFT-ENTR (since you don't say what version of excel you use - please update your profile).

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=TEXTJOIN(", ",,IF(Sheet2!$A$2:$C$100=B2,Sheet2!$A$1:$C$1,""))

 

[Candide]

I've updated my account to reflect 2021 Excel.

Thank you both so much for your help. Both formulas work well and I am slightly modifying them to suit my needs. Thank you both again!!!!!

 

[Fluff]

Glad we could help & thanks for the feedback.