SOLVED

Nested Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2479747%22%20slang%3D%22en-US%22%3ENested%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2479747%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%3C%2FP%3E%3CP%3EI%20need%20help%20pleas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(M4%26gt%3B80%2C%227%22%2C(M4%26gt%3B70%2C%226%22%2C(M4%26gt%3B60%2C%225%22)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2479747%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2479798%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2479798%22%20slang%3D%22en-US%22%3EPlace%20the%20numbers%20in%20a%20two-column%20table%2C%20similar%20to%20this%3A%3CBR%20%2F%3E%20A%20B%3CBR%20%2F%3E1%2060%205%3CBR%20%2F%3E2%2070%206%3CBR%20%2F%3E3%2080%207%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20use%20a%20formula%20like%20this%3A%3CBR%20%2F%3E%3DVLOOKUP(M4%2C%24A%241%3A%24B%243%2C2%2CTRUE)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2479836%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2479836%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1086819%22%20target%3D%22_blank%22%3E%40MarleneCunliff%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20and%20if%20you%20insist%20on%20using%20nested%20IF's%2C%20perhaps%20this%20is%20what%20you%20tried%20to%20achieve%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(M4%26gt%3B80%2C%227%22%2CIF(M4%26gt%3B70%2C%226%22%2CIF(M4%26gt%3B60%2C%225%22%2C%22x%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20%22x%22%20will%20be%20returned%20for%20any%20value%20in%20M4%20less%20than%20or%20equal%20to%2060.%20Remove%20the%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%2C%22x%22%3C%2FSTRONG%3E%3C%2FFONT%3E%20part%20and%20it%20will%20return%20FALSE%20in%20such%20case.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2480086%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2480086%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply%2C%20I%20got%20it%20to%20work%20as%20per%20your%20directions%2C%20however%20some%20fields%20give%20me%20a%20%23N%2FA%20value%20why%20is%20this%20as%20the%20array%20table%20does%20have%20the%20values%20it%20should.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good day

I need help pleas

 

IF(M4>80,"7",(M4>70,"6",(M4>60,"5")))

4 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

Place the numbers in a two-column table, similar to this:

  A  B
1 60 5
2 70 6
3 80 7



And use a formula like this:
=VLOOKUP(M4,$A$1:$B$3,2,TRUE)

@MarleneCunliff As a variant, and if you insist on using nested IF's, perhaps this is what you tried to achieve:

=IF(M4>80,"7",IF(M4>70,"6",IF(M4>60,"5","x")))

where "x" will be returned for any value in M4 less than or equal to 60. Remove the ,"x" part and it will return FALSE in such case.

@Jan Karel Pieterse 

Thank you for your reply, I got it to work as per your directions, however some fields give me a #N/A value why is this as the array table does have the values it should.

The table must be sorted in ascending order of the first column