how to nest more than 7 'if' functions

mgraviss

New Member
Joined
Jul 5, 2005
Messages
15
I need help!

Below is a table that I am trying to reference using a formula. I tried to use the formula below, but you can only nest 7 functions. So what is the best way to do the formula below for 20 nested functions? I made a truth table below, but I'm not sure how to use it.

=IF(AND(I3="A",G3="A"),"correct",IF(AND(I3="A",G3="B"),"correct",IF(AND(I3="A",G3="C"),"false blue",IF(AND(I3="A",G3="D"),"false blue",IF(AND(I3="B",G3="A"),"correct",IF(AND(I3="B",G3="B"),"correct correct",IF(AND(I3="B",G3="C"),"correct","false")))))))

How can I reference the table below to display a value in column C when given the combination of A and B? I also want the result to say "false" if none of the 'if/ands' are true.

I can send file if needed. Please advise. Thanks in advance. Matthew


Col A Col B Col C
Row1 Source Response Result
Row2 A A Correct
Row3 A B Correct
Row4 A C False Blue
Row5 A D False Blue
Row6 A E Correct
Row7 B A Correct
Row8 B B Correct
Row9 B C False Blue
Row10 B D False Blue
Row11 B E Correct
Row12 C A Missed
Row13 C B Missed
Row14 C C Correct
Row15 C D False Blue
Row16 C E False Red
Row17 D A Missed
Row18 D B Missed
Row19 D C Missed
Row20 D D Correct
Row21 D E False Red
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,416
Office Version
  1. 365
Platform
  1. Windows
Create a two column lookup table, where the first column has each combination of your two values (sewn together) and the second column has the result, i.e.
Code:
AA	Correct
AB	Correct
AC	False Blue
AD	False Blue
AE	Correct
BA	Correct
BB	Correct
BC	False Blue
BD	False Blue
BE	Correct
CA	Missed
CB	Missed
CC	Correct
CD	False Blue
CE	False Red
DA	Missed
DB	Missed
DC	Missed
DD	Correct
DE	False Red
Let's say that this table is in the range A2:B21. Then use a VLOOKUP formula to return your result, like this:
=VLOOKUP(I3&G3,A2:B21,2,0)

See Excel's help if you need more information/examples on the VLOOKUP function.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,458
Messages
5,572,249
Members
412,451
Latest member
newbie22922792
Top