Solved Nested IF/AND Function in Excel

October 28, 2014 at 00:19:06
Specs: Windows 7
I'm trying to build a spreadsheet that will allow me to compute 2 variables and return a value from one of 8 tables. I plan to have the tables in a separate sheet labeled "Tables" as they will only be used for calculation purposes.

So far my formula is...
=IF(AND(B4<6,B4>0),IF(C4="D",Tables!B3:B6,IF(C4="N",Tables!C3:C6,IF(C4="Q",Tables!D3:D6,IF(C4="H",Tables!E3:E6))))),
IF(AND(B4<16,B4>5),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12))))),
IF(AND(B4<31,B4>15),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12))))),
IF(AND(B4<51,B4>30),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12))))),
IF(AND(B4<76,B4>50),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12))))),
IF(AND(B4<101,B4>75),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12))))),
IF(AND(B4<151,B4>100),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12))))),
IF(AND(B4<201,B4>150),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12)))))

Not sure where I'm going wrong?


See More: Nested IF/AND Function in Excel

Report •

✔ Best Answer
October 28, 2014 at 13:26:49
I don't know why I keep playing with this without knowing the detail behind the problem, but we might be able to reduce the formula to something as "simple" as this along with a lookup table containing the values and the ranges:

=INDIRECT("Tables!"&HLOOKUP(C4,$I$1:$L$10,MATCH(B4,$H$1:$H$10,1)))


      H     I        J        K      L
1	    D	     N	      Q	     H
2      1  B3:B6	   C3:C6    D3:D6   E3:E6
3      6  B9:B12    etc.     etc.    etc.
4     16  B15:B18			
5     31   etc.			
6     51   etc.			
7     76   etc.			
8    101   etc.			
9    151   etc.			
10   201   etc.			


HLOOKUP would lookup the letter from C4 and MATCH would lookup the number in B4 and use that info to pull the range (e.g. B3:B6) from the table.

INDIRECT would then use that range to pull the actual data from the Tables sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 28, 2014 at 00:33:46
Noticed a redundant typo in the table designations. Here is my actual (yet non functional) formula.

=IF(AND(B4<6,B4>0),IF(C4="D",Tables!B3:B6,IF(C4="N",Tables!C3:C6,IF(C4="Q",Tables!D3:D6,IF(C4="H",Tables!E3:E6))))),
IF(AND(B4<16,B4>5),IF(C4="D",Tables!B9:B12,IF(C4="N",Tables!C9:C12,IF(C4="Q",Tables!D9:D12,IF(C4="H",Tables!E9:E12))))),
IF(AND(B4<31,B4>15),IF(C4="D",Tables!B15:B18,IF(C4="N",Tables!C15:C18,IF(C4="Q",Tables!D15:D18,IF(C4="H",Tables!E15:E18))))),
IF(AND(B4<51,B4>30),IF(C4="D",Tables!B21:B24,IF(C4="N",Tables!C21:C24,IF(C4="Q",Tables!D21:D24,IF(C4="H",Tables!E21:E24))))),
IF(AND(B4<76,B4>50),IF(C4="D",Tables!B27:B30,IF(C4="N",Tables!C27:C30,IF(C4="Q",Tables!D27:D30,IF(C4="H",Tables!E27:E30))))),
IF(AND(B4<101,B4>75),IF(C4="D",Tables!B32:B33,IF(C4="N",Tables!C32:C33,IF(C4="Q",Tables!D32:D33,IF(C4="H",Tables!E32:E33))))),
IF(AND(B4<151,B4>100),IF(C4="D",Tables!B36:B37,IF(C4="N",Tables!C36:C37,IF(C4="Q",Tables!D36:D37,IF(C4="H",Tables!E36:E37))))),
IF(AND(B4<201,B4>150),IF(C4="D",Tables!B40:B41,IF(C4="N",Tables!C40:C41,IF(C4="Q",Tables!D41:D41,IF(C4="H",Tables!E40:E41)))))


Report •

#2
October 28, 2014 at 07:21:19
re: "Not sure where I'm going wrong?"

"Going wrong" could mean a number of things. It would help us help you if you told us exactly what you mean by "going wrong".

- Is Excel popping up an error as soon as you try to enter the formula?
- Are you not getting the correct result for any and all conditions?
- Are you not getting the correct result for some but not all of the conditions?
- Are you getting a result, but the result is an error, e.g. #VALUE?
- etc.

Since we have no idea what you are trying to do (other than "compute 2 variables and return a value") or what your workbook looks like, it's hard for us to tell you what is wrong, especially with a formula as long as the one you have posted.

Please provide some more detail and we'll see what we can do to help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
October 28, 2014 at 09:25:21
This is just a "By The Way" while I await your response to my previous post...

Whenever I see a long list of IF's trying to determine if a value is in between a number of different ranges, I usually check to see if a VLOOKUP using the range_lookup argument might be a better idea.

For example,

The following "Nested IF/AND" formula can be replaced with a much shorter VLOOKUP formula and a table with the values/results. If I ever need to alter the criteria or the desired results, I can simply change the table and not have to touch the formula.

=IF(AND(B4<6,B4>0),"Between 1 & 5",IF(AND(B4<16,B4>5),"Between 6 & 15",IF(AND(B4<31,B4>15),"Between 16 &30",IF(AND(B4<51,B4>30),"Between 31 & 50","Greater than 50"))))

vs.

=VLOOKUP(B4,$H$1:$I$5,2,1)

Using this Lookup table:

      H              I

1     1         Between 1 & 5
2     6         Between 6 & 15
3    16         Between  16 & 30
4    31         Between 31 & 50
5    51         Greater than 50

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
October 28, 2014 at 13:26:49
✔ Best Answer
I don't know why I keep playing with this without knowing the detail behind the problem, but we might be able to reduce the formula to something as "simple" as this along with a lookup table containing the values and the ranges:

=INDIRECT("Tables!"&HLOOKUP(C4,$I$1:$L$10,MATCH(B4,$H$1:$H$10,1)))


      H     I        J        K      L
1	    D	     N	      Q	     H
2      1  B3:B6	   C3:C6    D3:D6   E3:E6
3      6  B9:B12    etc.     etc.    etc.
4     16  B15:B18			
5     31   etc.			
6     51   etc.			
7     76   etc.			
8    101   etc.			
9    151   etc.			
10   201   etc.			


HLOOKUP would lookup the letter from C4 and MATCH would lookup the number in B4 and use that info to pull the range (e.g. B3:B6) from the table.

INDIRECT would then use that range to pull the actual data from the Tables sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question