• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF vs Vlookup

K

Krishnan

New Member
Hi Folks!
Trust all are safe & well!

I'm trying to set up a formula to enable calculation of commissions for a particular Unit to ensure accurate monthly payout.
Tried a few options IFS, Vlookup and a combination...also stumbled on some solutions with Index & Match as well (you can see I'm lost :-(

Sharing a brief and workbook.

Thanks in advance.
Krishnan
 

Attachments

  • Learn xl_Commission Structure.xlsx
    20.1 KB · Views: 5
Hi, Krishnan,

The formula would be the following:

=VLOOKUP([@Designation];CommStructure;MATCH(F$26;CommStructure[#Headers];0);0)*[@Revenue]

Once you copy the formula, F$26 is dynamically referring to the names ("Unit Revenue" / "Agent" / "Officer" / "Manager") described in your Comm.structure.

See attached file, if it suits your needs.

Cheers,
Romeo
 

Attachments

  • Learn xl_Commission Structure_revised Romeo.xlsx
    20.2 KB · Views: 6
Last edited by a moderator:
RomeoNastase said:
Hi, Krishnan,

The formula would be the following:

=VLOOKUP([@Designation];CommStructure;MATCH(F$26;CommStructure[#Headers];0);0)*[@Revenue]

Once you copy the formula, F$26 is dynamically referring to the names ("Unit Revenue" / "Agent" / "Officer" / "Manager") described in your Comm.structure.

See attached file, if it suits your needs.

Cheers,
Romeo
Click to expand...

Hi Romeo,

Thanks a million...worked like magic. Guess I'll need to dive a bit deeper into Match combinations ...

Would this have worked with IFS?

Cheers!
K
 
Krishnan said:
Hi Romeo,

Thanks a million...worked like magic. Guess I'll need to dive a bit deeper into Match combinations ...

Would this have worked with IFS?

Cheers!
K
Click to expand...
Hi, Krishnan,

You are welcomed!

You may use also IFS, but I think your formula will grow bigger and bigger if your commission structure will be larger.
For example, if you would have had three more roles ("Super Manager", "Head of Sales" and "CEO"), with their respective commission shares:
Using the formula I have suggested, you would just need to expand your report with three additional columns for the new roles and simply copy-paste the formula.
On the other hand, using the IFS function would require to re-write the formula, by adding 3 more IFS nested conditions...

Cheers!
Romeo
 
You must log in or register to reply here.
Back
Top