Vlookup a rate * %, based on two different award/groups

Kelli Webb

Member
Hello, I hope you are travelling ok with the current global lockdown

I have figured out how to do the multiplication I required based on one Award Level i.e. LSA

The value in C5 should be based on the

Award & Level selected in C3 x

(Select the Age in E7, I need this to apply the relevant % from the table in K2:M15) x

Multiplied by the Adult rate in C12:C16

The correct answers are between column E & I based on the Award Level & age selected

I find it so hard to articulate what I want to do, which I feel is just as bad at trying to figure out the calculation.

thank you, as always forever in your debt.

Kelli

Attachments

• 12.1 KB Views: 5

Try:

Attachments

• 12.3 KB Views: 6

Kelli Webb

Member
Hi P45cal,

I've been working with your formula and it has been working great.

Can I push the envelope even further by adding one more condition i.e. if Casual

In the attached in C9 I've added a drop down box to select Casual, IF Casual it is the same result multiplied by 1.25 i.e. plus 25% (everyone else is 100%)

thank you,

Attachments

• 12.8 KB Views: 5

GraH - Guido

Well-Known Member
Hi Kelli,

Maybe like this, adding the blue part to the existing formula.
=IF(\$E\$9="Casual";1,25;1)*SUM(VLOOKUP(\$E\$7;\$L\$6:\$M\$14;2)*VLOOKUP(\$C\$3;\$B\$13:\$C\$17;2;0)*38)*52
or
=(1+(\$E\$9="Casual")*0.25)*SUM(VLOOKUP(\$E\$7;\$L\$6:\$M\$14;2)*VLOOKUP(\$C\$3;\$B\$13:\$C\$17;2;0)*38)*52

Attachments

• 12.9 KB Views: 3

Peter Bartholomew

Well-Known Member
Just for the sake of being different, I used a two-way XLOOKUP.
Code:
``````= ( XLOOKUP(awardLevel, awardLevelHdr, rate)
XLOOKUP(age, ageHdr, rate) )
* IF( age="adult", 52, 1 )
* IF( status="Casual", 1.25, 1 )``````
The first lookup returns a row from the rate data table, whilst the second returns the column appropriate to the age. Since both are range references, they may be intersected to give the required rate.
The multiplicative factors allow for casual employment and the fact that the adult rate is quoted weekly rather than annual.

By the way, the rates I inserted for the shaded out areas do not agree with the values returned by the existing formulae.

Attachments

• 14.2 KB Views: 2