Forum Discussion

Deleted's avatar
Deleted
Jun 27, 2018

Need 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_Lewin's avatar
    Detlef_Lewin
    Silver 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)
    • Deleted's avatar
      Deleted
      Thank you Detlef
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      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)
    • Deleted's avatar
      Deleted
      Thank you Sergei. The formula doesn't seem to work, but I found another way.

      thank you for your help
      • Hi 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

         

Resources