Multiple IF functions with multiple conditions

[RJB78]

Hello -

Problem: I am trying to get a formula to display the letter "G", "D", or "GI" based on the values of two other cells.

Example:

Symbol G,D,GI % Yes/No
A G 0.25% Y
B G 5.00% N
C G 3.00% #N/A
D D 4.00% Y
E GI 2.50% Y

Rules:

G = If Yes/No column = #N/A, then I want the G,D,GI column to display the letter 'G'. (The #N/A is from a vlookup not finding a value)
If Yes/No column = N, then I want the G,D,GI column to display the letter 'G'
If Yes/No column = Y, Then I want the G,D,GI column to display the letter 'G', only if the percentage in the % column is greater than or equal to 0 and less than .005 (between 0 and .049(mutual exclusivity))

GI = If Yes/No column = Y, then I want the G,D,GI column to display the letters 'GI', only if the percentage in the % column is greater than or equal to .005 and less than .04 (between .005 and .039)(mutual exclusivity))

D = If Yes/No column= Y, then I want the G,D,GI column to display the letter 'D', only if the percentage in the % column is greater than or equal to .04

I have some individual formulas for some of the rules, but I am not sure how to do the formula for the "G" '#N/A' rule and combine them all the rule formulas to work together.

"G" 'N' Rule: =IF(D2="N","G")
"G" 'Y' Rule: =IF(AND(D2="Y",C2>=0,C2<.005),"G")

GI Rule: =IF(AND(D2="Y",C2>=.005,C2<0.04),"GI")

D Rule: = IF(AND(D2="Y",C2>=.04),"D")

If there is no solution for the "G" 'N/A' rule I suppose I could use the iferror function to return a 'N/A'. Correct?


Thank you in advance, I really appreciate any and all assistance.

 

[Special-K99]

Try

=IF(OR(C1="#N/A",C1="N",AND(C1="Y",B1>=0,B1<0.005)),"G",IF(AND(C1="Y",B1>=0.005,B1<=0.04),"GI",IF(AND(C1="Y",B1>0.04),"D","#N/A")))

 

[oldbrewer]

=if(d2="n/a","g",IF(D2="N","G",IF(AND(D2="Y",C2>=0,C2<.005),"G",IF(AND(D2="Y",C2>=.005,C2<0.04),"GI", IF(AND(D2="Y",C2>=.04),"D",)))))

 

[RJB78]

That all worked, except for the #N/A part.

Thank you very much. That is a huge help

 

[RJB78]

@Special K-99 and @oldbrewer

That all worked, except for the #N/A part.

Thank you very much. This is a huge help

 

[oldbrewer]

need to know what sort of an n/a it is is it a non match in a search formula, for example

 

[RJB78]

@oldbrewer

The N/A is a non match from a vlookup. I just changed the original vlookup to a iferror(vlookup(),"N/A")

Do you have a better recommendation? Or a fix to the original formula you sent?

Thank you

 

[oldbrewer]

pink	#VALUE!							blue	3
pink	4							green	4
								red	5
		searching for pink in my ref table gives #value
		looking up pink does not give an n/a
		how are you getting it please

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

pink	#VALUE!							blue	3
azure	#N/A	5	8					green	4
								red	5
		searching for pink in my ref table gives #value
		I got the #n/a with azure
				HH
				=IF(ISNA(B2)=TRUE,"HH","TT")
				so use ISNA

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[RJB78]

As an example:

A =vlookup(A,1:2,2,false) = Y A Y
B =vlookup(B,1:2,2,false) = #N/A C N
C =vlookup(C,1:2,2,false) = N


I get the #N/A because "B" does not exist on the other sheet