V Look Up Function

[akbar]

Dear All,

Please find the attached file,

I Have Two Tables in the sheet,what i'm trying to sort out is need to get the gross salary of an employee
based on his Name,Badge No,OT Rate,T Rate & Basic.

If Badge No & Name which is A2 & C2 then it should check on the table below A19:E27 then
it should take on the rates which is OT,T & Basic,Multiply OT Hours with OT Rate then Multiply T Rate
with T Hours and finally it should add the basic salary along with that and the end result should be on F2..

Any Help Will Be Appreciated...

Thanks,
Ak

[Ace_XL]

In F2

Please Login or Register  to view this content.

[akbar]

Thanks for the help Ace,but it seems there is an error in the formula,when i copy down the formula it doesn't give an exact result,
I think 2nd and 3rd Match $C$2 is stable doesn't change..

=SUM(INDEX(C$19:C$27,MATCH($C3,$B$19:$B$27,0))*D3,INDEX(D$19:D$27,MATCH($C$2,$B$19:$B$27,0))*E3,INDEX(E$19:E$27,MATCH($C$2,$B$19:$B$27,0)))

Regards,
Ak

[Ace_XL]

Apologies..try this

Please Login or Register  to view this content.

[akbar]

Thanks Ace it helped..

Regards,
Ak

[Teethless mama]

Try this version:

=SUMPRODUCT((A$19:A$24=A2)*(B$19:B$24=C2),(C$19:C$24*D2)+(D$19:D$24*E2)+E$19:E$24)

[akbar]

Thanks Mama both the formulas work fine..

Regards,
Ak