Help with spread sheet please.

[steiny0831]

Hi there,

Am running into a problem with a spread sheet I am working on.

I attached a link to a test page I am working on.

What I need it to do is -

If cell A2 is equal to any word in the range of G1:G4 and B2 is 11 then I need it to return "yes" in C2, if not it needs to return the value of B2 in cell C2.

I cant get it to work in trying a range of cells in G1:G4, it will only work if I have the word color itself in the formula. But need it to work in a range of cells.

Below is a shared sheet with the top not working and bottom working but only by word color in formula.

Not sure if this makes sense.

https://docs.google.com/spreadsheets/d/1abFq3aAR2UoF9Vd0akuEFcafFINDwvXAK_dZmkV_1Gw/edit?usp=sharing

Thank you for any help!

 

[Fluff]

How about
=IF(AND(ISNUMBER(MATCH(A2,G$1:G$4,0)),B2="11"),"Yes",B2)

 

[jtakw]

Hi,

Your Column B numbers are stored as Text in your sample, so my formula looks for the Text 11 (eleven), if you convert Column B Text numbers to Real Numbers, remove the Quote marks around 11 in my formula:

Book1
	A	B	C	D	E	F	G
1	Color	size	result				red
2	red	11	Yes				orange
3	black	15	15				black
4	white	11	11				green
Sheet353

Cell Formulas
Range	Formula
C2	=IF(AND(COUNTIF(G$1:G$4,A2),B2="11"),"Yes",B2)

 

[steiny0831]

How about
=IF(AND(ISNUMBER(MATCH(A2,G$1:G$4,0)),B2="11"),"Yes",B2)

Yes, this is what I needed. Thank you very much!

 

[jtakw]

Did you try my formula also??

 

[Fluff]

Glad we could help & thanks for the feedback