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?

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

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)))))

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

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_lookupargument 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

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.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History