Engineering the Digital Civilization

Search Here...

Wednesday, 26 October 2016

Switch Case or Multiple If Else Nesting in Excel | HYONKOWS

Switch Case in Excel

Under many circumstances there is a need of if else or logical switch to select a particular mode of operation during calculations especially if you're a nerd at using excel and taking it to its limits.
One of my friend had a concept which he wanted to apply on excel but was lagging with the technique of implementation, after a lot of  R&D I finally ended up with a Jugaad in which I used HLOOKUP function of excel to drive case switches.

How was it Done?

Basically the video below explains it well, though I'd like to explain the technique:

Assuming you've seen the video, here are the details of the logic I used!

  • As the HLOOKUP wasn't working well for the formulas I made the columns of the formulas in the table itself and then just added the HLOOKUP as the switch to toggle the Output. All the four cases are calculated but only one case is preceed towards the output.
  • Now while making the array to be selected it is to be noted that the table that you select should have the same formatting, in my case as I'd selected the table headers it got up all messed up.
  • Now as I'm using HLOOKUP so my 1st row would be 45, 90, 180, 360 row and and so on. So to automate the row selection procedure I started numbering from 2 onwards and mentioned it as the reference in the syntax for the HLOOKUP operation.

Its better to Hide up formulas that you used! so just select the columns and hide those.

You can still manupulate with the cases by just entering the value and I'll put the corresponding value in the cell

How to use the HLOOKUP formula?

Hope this post was usefull

Comment your experience in the section below!

No comments:

Post a Comment