# 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

✔ 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.

#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.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 ```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.

Report • 