Invoice amount based on category times the amount

[kcfungus]

Hello gurus!

Hoping you could assist.

I am trying to get the automated formula for invoice amount for each row, based on the category selected times the amount

E.G. if Row 2, Category is selected as SpPBS, the sum of Column B-D adds up times rate 214.41.
However, if Row 3, Category is selected as BMP, the sum of Column B-D adds up times rate 193.99.

I am able to get the correct formula for individual only but cannot somehow make them combine together in one.

WOuld be great if someone could guide me!

Thanks again!


Book1.xlsx

[AliGW]

Still using Excel 2019?

[AliGW]

In E2 copied down:

=IF($A2="","",SUM(B2:D2)*LOOKUP(A2,{"BMP","SpPBS"},{193.99,214.41}))

Note: the values in the lookup must be in alphabetical order.

[kcfungus]

In E2 copied down:

=IF($A2="","",SUM(B2:D2)*LOOKUP(A2,{"BMP","SpPBS"},{193.99,214.41}))

Note: the values in the lookup must be in alphabetical order.

Hello,

Sorry yes still using excel 2019.
Thanks for looking into this very quick and it worked - never thought to use lookup in this occasion.