Forum Discussion
MULTIPLE IF CONDTITIONS OVER 64
Greetings!
I have a formula with more than 64 nested items and I need to simplify it so that I can reference a labor matrix.
=IF(B2=0.1,Gas!C35,IF(B2=0.2,Gas!D35,IF(B2=0.3,Gas!E35,IF(B2=0.4,Gas!F35,IF(B2=0.5,Gas!G35,IF(B2=0.6,Gas!H35,IF(B2=0.7,Gas!I35,IF(B2=0.8,Gas!J35,IF(B2=0.9,Gas!K35,IF(B2=1.0,Gas!B36,IF(B2=1.1,Gas!C36,IF(B2=1.2,Gas!D36,IF(B2=1.3,Gas!E36,IF(B2=1.4,Gas!F36,IF(B2=1.5,Gas!G36,IF(B2=1.6,Gas!H36,IF(B2=1.7,Gas!I36,IF(B2=1.8,Gas!J36,IF(B2=1.9,Gas!K36,IF(B2=2.0,Gas!B37,IF(B2=2.1,Gas!C37,IF(B2=2.2,Gas!D37,IF(B2=2.3,Gas!E37,IF(B2=2.4,Gas!F37,IF(B2=2.5,Gas!G37,IF(B2=2.6,Gas!H37,IF(B2=2.7,Gas!I37,IF(B2=2.8,Gas!J37,IF(B2=2.9,Gas!K37,IF(B2=3.0,Gas!B38,IF(B2=3.1,Gas!C38,IF(B2=3.2,Gas!D38,IF(B2=3.3,Gas!E38,IF(B2=3.4,Gas!F38,IF(B2=3.5,Gas!G38,IF(B2=3.6,Gas!H38,IF(B2=3.7,Gas!I38,IF(B2=3.8,Gas!J38,IF(B2=3.9,Gas!K38,IF(B2=4.0,Gas!B39,IF(B2=4.1,Gas!C39,IF(B2=4.2,Gas!D39,IF(B2=4.3,Gas!E39,IF(B2=4.4,Gas!F39,IF(B2=4.5,Gas!G39,IF(B2=4.6,Gas!H39,IF(B2=4.7,Gas!I39,IF(B2=4.8,Gas!J39,IF(B2=4.9,Gas!K39,IF(B2=5.0,Gas!B40,IF(B2=5.1,Gas!C40,IF(B2=5.2,Gas!D40,IF(B2=5.3,Gas!E40,IF(B2=5.4,Gas!F40,IF(B2=5.5,Gas!G40,IF(B2=5.6,Gas!H40,IF(B2=5.7,Gas!I40,IF(B2=5.8,Gas!J40,IF(B2=5.9,Gas!K40,IF(B2=6.0,Gas!B41,IF(B2=6.1,Gas!C41,IF(B2=6.2,Gas!D41,IF(B2=6.3,Gas!E41,IF(B2=6.4,Gas!F41,IF(B2=6.5,Gas!G41,IF(B2=6.6,Gas!H41,IF(B2=6.7,Gas!I41,IF(B2=6.8,Gas!J41,IF(B2=6.9,Gas!K41,IF(B2=7.0,Gas!B42,IF(B2=7.1,Gas!C42,IF(B2=7.2,Gas!D42,IF(B2=7.3,Gas!E42,IF(B2=7.4,Gas!F42,IF(B2=7.5,Gas!G42,IF(B2=7.6,Gas!H42,IF(B2=7.7,Gas!I42,IF(B2=7.8,Gas!J42,IF(B2=7.9,Gas!K42,IF(B2=8.0,Gas!B43,IF(B2=8.1,Gas!C43,IF(B2=8.2,Gas!D43,IF(B2=8.3,Gas!E43,IF(B2=8.4,Gas!F43,IF(B2=8.5,Gas!G43,IF(B2=8.6,Gas!H43,IF(B2=8.7,Gas!I43,IF(B2=8.8,Gas!J43,IF(B2=8.9,Gas!K43,IF(B2=9.0,Gas!B44,IF(B2=9.1,Gas!C44,IF(B2=9.2,Gas!D44,IF(B2=9.3,Gas!E44,IF(B2=9.4,Gas!F44,IF(B2=9.5,Gas!G44,IF(B2=9.6,Gas!H44,IF(B2=9.7,Gas!I44,IF(B2=9.8,Gas!J44,IF(B2=9.9,Gas!K44,IF(B2=10.0,Gas!B45,IF(B2=10.1,Gas!C45,IF(B2=10.2,Gas!D45,IF(B2=10.3,Gas!E45,IF(B2=10.4,Gas!F45,IF(B2=10.5,Gas!G45,IF(B2=10.6,Gas!H45,IF(B2=10.7,Gas!I45,IF(B2=10.8,Gas!J45,IF(B2=10.9,Gas!K45,IF(B2=11.0,Gas!B46,IF(B2=11.1,Gas!C46,IF(B2=11.2,Gas!D46,IF(B2=11.3,Gas!E46,IF(B2=11.4,Gas!F46,IF(B2=11.5,Gas!G46,IF(B2=11.6,Gas!H46,IF(B2=11.7,Gas!I46,IF(B2=11.8,Gas!J46,IF(B2=11.9,Gas!K46,IF(B2=12.0,Gas!B47,IF(B2=12.1,Gas!C47,IF(B2=12.2,Gas!D47,IF(B2=12.3,Gas!E47,IF(B2=12.4,Gas!F47,IF(B2=12.5,Gas!G47,IF(B2=12.6,Gas!H47,IF(B2=12.7,Gas!I47,IF(B2=12.8,Gas!J47,IF(B2=12.9,Gas!K47,IF(B2=13.0,Gas!B48,IF(B2=13.1,Gas!C48,IF(B2=13.2,Gas!D48,IF(B2=13.3,Gas!E48,IF(B2=13.4,Gas!F48,IF(B2=13.5,Gas!G48,IF(B2=13.6,Gas!H48,IF(B2=13.7,Gas!I48,IF(B2=13.8,Gas!J48,IF(B2=13.9,Gas!K48,IF(B2=14.0,Gas!B49,IF(B2=14.1,Gas!C49,IF(B2=14.2,Gas!D49,IF(B2=14.3,Gas!E49,IF(B2=14.4,Gas!F49,IF(B2=14.5,Gas!G49,IF(B2=14.6,Gas!H49,IF(B2=14.7,Gas!I49,IF(B2=14.8,Gas!J49,IF(B2=14.9,Gas!K49,IF(B2=15.0,Gas!B50,IF(B2=15.1,Gas!C50,IF(B2=15.2,Gas!D50,IF(B2=15.3,Gas!E50,IF(B2=15.4,Gas!F50,IF(B2=15.5,Gas!G50,IF(B2=15.6,Gas!H50,IF(B2=15.7,Gas!I50,IF(B2=15.8,Gas!J50,IF(B2=15.9,Gas!K50)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Any help is greatly appreciated!
- For multiple nested if, it is better to use IFS formula
=IFS(B2=0.1,Gas!C35,B2=0.2,Gas!D35,B2=0.3,Gas!E35,B2=0.4,Gas!F35,B2=0.5,Gas!G35,B2=0.6,Gas!H35,B2=0.7,Gas!I35,B2=0.8,Gas!J35,B2=0.9,Gas!K35,B2=1.0,Gas!B36,B2=1.1,Gas!C36,B2=1.2,Gas!D36,B2=1.3,Gas!E36,B2=1.4,Gas!F36,B2=1.5,Gas!G36,B2=1.6,Gas!H36,B2=1.7,Gas!I36,B2=1.8,Gas!J36,B2=1.9,Gas!K36,B2=2.0,Gas!B37,B2=2.1,Gas!C37,B2=2.2,Gas!D37,B2=2.3,Gas!E37,B2=2.4,Gas!F37,B2=2.5,Gas!G37,B2=2.6,Gas!H37,B2=2.7,Gas!I37,B2=2.8,Gas!J37,B2=2.9,Gas!K37,B2=3.0,Gas!B38,B2=3.1,Gas!C38,B2=3.2,Gas!D38,B2=3.3,Gas!E38,B2=3.4,Gas!F38,B2=3.5,Gas!G38,B2=3.6,Gas!H38,B2=3.7,Gas!I38,B2=3.8,Gas!J38,B2=3.9,Gas!K38,B2=4.0,Gas!B39,B2=4.1,Gas!C39,B2=4.2,Gas!D39,B2=4.3,Gas!E39,B2=4.4,Gas!F39,B2=4.5,Gas!G39,B2=4.6,Gas!H39,B2=4.7,Gas!I39,B2=4.8,Gas!J39,B2=4.9,Gas!K39,B2=5.0,Gas!B40,B2=5.1,Gas!C40,B2=5.2,Gas!D40,B2=5.3,Gas!E40,B2=5.4,Gas!F40,B2=5.5,Gas!G40,B2=5.6,Gas!H40,B2=5.7,Gas!I40,B2=5.8,Gas!J40,B2=5.9,Gas!K40,B2=6.0,Gas!B41,B2=6.1,Gas!C41,B2=6.2,Gas!D41,B2=6.3,Gas!E41,B2=6.4,Gas!F41,B2=6.5,Gas!G41,B2=6.6,Gas!H41,B2=6.7,Gas!I41,B2=6.8,Gas!J41,B2=6.9,Gas!K41,B2=7.0,Gas!B42,B2=7.1,Gas!C42,B2=7.2,Gas!D42,B2=7.3,Gas!E42,B2=7.4,Gas!F42,B2=7.5,Gas!G42,B2=7.6,Gas!H42,B2=7.7,Gas!I42,B2=7.8,Gas!J42,B2=7.9,Gas!K42,B2=8.0,Gas!B43,B2=8.1,Gas!C43,B2=8.2,Gas!D43,B2=8.3,Gas!E43,B2=8.4,Gas!F43,B2=8.5,Gas!G43,B2=8.6,Gas!H43,B2=8.7,Gas!I43,B2=8.8,Gas!J43,B2=8.9,Gas!K43,B2=9.0,Gas!B44,B2=9.1,Gas!C44,B2=9.2,Gas!D44,B2=9.3,Gas!E44,B2=9.4,Gas!F44,B2=9.5,Gas!G44,B2=9.6,Gas!H44,B2=9.7,Gas!I44,B2=9.8,Gas!J44,B2=9.9,Gas!K44,B2=10.0,Gas!B45,B2=10.1,Gas!C45,B2=10.2,Gas!D45,B2=10.3,Gas!E45,B2=10.4,Gas!F45,B2=10.5,Gas!G45,B2=10.6,Gas!H45,B2=10.7,Gas!I45,B2=10.8,Gas!J45,B2=10.9,Gas!K45,B2=11.0,Gas!B46,B2=11.1,Gas!C46,B2=11.2,Gas!D46,B2=11.3,Gas!E46,B2=11.4,Gas!F46,B2=11.5,Gas!G46,B2=11.6,Gas!H46,B2=11.7,Gas!I46,B2=11.8,Gas!J46,B2=11.9,Gas!K46,B2=12.0,Gas!B47,B2=12.1,Gas!C47,B2=12.2,Gas!D47,B2=12.3,Gas!E47,B2=12.4,Gas!F47,B2=12.5,Gas!G47,B2=12.6,Gas!H47,B2=12.7,Gas!I47,B2=12.8,Gas!J47,B2=12.9,Gas!K47,B2=13.0,Gas!B48,B2=13.1,Gas!C48,B2=13.2,Gas!D48,B2=13.3,Gas!E48,B2=13.4,Gas!F48,B2=13.5,Gas!G48,B2=13.6,Gas!H48,B2=13.7,Gas!I48,B2=13.8,Gas!J48,B2=13.9,Gas!K48,B2=14.0,Gas!B49,B2=14.1,Gas!C49,B2=14.2,Gas!D49,B2=14.3,Gas!E49,B2=14.4,Gas!F49,B2=14.5,Gas!G49,B2=14.6,Gas!H49,B2=14.7,Gas!I49,B2=14.8,Gas!J49,B2=14.9,Gas!K49,B2=15.0,Gas!B50,B2=15.1,Gas!C50,B2=15.2,Gas!D50,B2=15.3,Gas!E50,B2=15.4,Gas!F50,B2=15.5,Gas!G50,B2=15.6,Gas!H50,B2=15.7,Gas!I50,B2=15.8,Gas!J50,B2=15.9,Gas!K50,TRUE,0)
Excel provides multiple solution for similar one issue. Something like
= TEXTJOIN(",",, IF(lookupArray=testCell,returnArray,"") ) or = TOROW( IF(lookupArray=testCell, returnArray, NA()), 3)would pick out terms from the return array that correspond to a matching term in the lookup array.
I didn't look at every single item but it sure looks like an INDEX should work here something like:
=INDEX(Gas!A34:K50, INT(B2)+1, MOD(B2,1)+1)EDIT: I added 1 to the row and cols and changed the ref range to accommodate the 0 cases
- How do I make it populate into C2 the correct value from the grid. Example, in B2 I want to enter 2.3 and it pull the value from E37 or 2.3. B2 will be a cell that is manually entered labor hours, 1, 2.2, 3.3, etc.
Thank you for your assistance!- From what I see, you want a value in B2 to define the location in the grid such that the integer portion indicates the row and the decimal portion is the column. so the formula:
=INDEX(Gas!A34:K50, INT(B2)+1, MOD(B2,1)+1)
says to start at cell A34 and then move down the integer portion of B2 +1 rows and to the right the decimal portion of B2 +1 columns.
so in you example the value 2.3 would move down 3 rows (2+1) and to the right 4 columns (3+1) so starting at A34 would go down to row 37 and to the right to Col E and return the value in cell E37.
