Forum Discussion
Deleted
Jun 27, 2018Need Help Changing Nested IF formulas to Vlookups
Hi there,
I am in the process of trying to clean up Nested IF(AND formulas and converting them to Vlookup tables. I am having a very difficult time trying to figure out how to create some of these tables.
Here is an example of a formula that I am trying to create a table for:
=IF(AND(Shift=8,CB4>=10,CD4=2018),80,IF(AND(Shift=8,CB4<=10,CD4=2018),CB4*8,
IF(AND(Shift=9,CB4>=10,CD4=2018),90,IF(AND(Shift=9,CB4<=10,CD4=2018),CB4*9,
IF(AND(Shift=12,CB4>=8,CD4=2018),96,IF(AND(Shift=12,CB4<=8,CD4=2018),CB4*12,
IF(AND(Shift=10.5,CB4>=8,CD4=2018),84,IF(AND(Shift=10.5,CB4<=8,CD4=2018),CB4*10.5,FALSE))))))))
Would anyone have any suggestions of how to go about this?
Thank you
- Detlef_LewinSilver Contributor
Kristin,
not fully tested.
=SWITCH(SIGN(CB4-LOOKUP(Shift,{8,10;9,10;10.5,8;12,8})),-1,CB4*Shift,LOOKUP(Shift,{8,10;9,10;10.5,8;12,8})*Shift)*(CD4=2,018)
- DeletedThank you Detlef
Detlef, with comment what SWITCH() is only for Office 365 subscribers
Hi Kristin,
Perhaps
= IFERROR(IF(CD4=2018, MIN(INDEX({80,90,84,96},MATCH(Shift,{8,9,10.5,12},0)),CB4*Shift), FALSE),FALSE)
- DeletedThank you Sergei. The formula doesn't seem to work, but I found another way.
thank you for your helpHi Kristin,
That's okay, thank you for the feedback. If you have time could you please to clarify what's wrong with the formula - on my fast test all three give the same result
and attached.
Thank you