I'm desperately trying to work out the formula for a project pricing tool i'm working on with my team.

We cost jobs in multiple currencies, and each role has a different rate based on the currency.

In my costing tool i have a dropdown for currency, let's use Pounds and EURO for now.

Then in my costing table (same sheet) i have dropdowns on each line item (row) that allows me to enter the role that will handle that tasks.

So in cell i1 is a drop down with currency (€,£) In column H is a drop down for role.

Based on the selection in column H i want to return either the € or £ rate is columns d or e respectively.

Any help would be greatly appreciated! Thank you.

CodePudding user response：

If it is only two currencies, then:

=LOOKUP( H4, $C$6:$C$13, IF( I1 = "EURO", $D$6:$D$13, $E$6:$E$13))

If more than two currencies, then use an INDEX MATCH, but your I1 needs to match the currency column headers. For example:

=INDEX( $D$6:$F$13, MATCH( H4, $C$6:$C$13, 0 ), MATCH( $I$1, $D$5:$F$5, 0 ) )

CodePudding user response：

Lou,

Ok, here's a mockup. You'll notice that I used Range names for the tables (see bottom of sheet). The formula shown in the formula bar is in I4 and is dragable down the column. The formula uses two VLookups the inner one to retrieve the currency column offset for use in the outer one that looks up the role.

Note: the items under Role must exactly match those in Col A so I suggest doing copy/paste operations.