... this is not possible without entering a formula.
Yes, it is. Use your calculator and then type the result manually into the cell. Then we can work out a formula that does what you need.  Reply With Quote

2. Let me know how close this is to what you want. Copy and paste this into D5:

=IFNA(INDEX(\$D\$19:\$D\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63<>C5)*(\$D\$19:\$D\$63<>""),0))*INDEX(\$D\$67:\$D\$74,MATCH(INDEX(\$C\$19:\$C\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63<>C5)*(\$D\$19:\$D\$63<>""),0)),\$C\$67:\$C\$74,0)),INDEX(\$D\$19:\$D\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63=C5),0)))

and then confirm it by pressing CTRL+SHIFT+ENTER (not just ENTER). You will see that curly brackets {} will appear round it (don't try to type these in - it won't work). Next, drag copy the formula down.

What it does is this (which is what I have attempted to derive from your incorrect formula):

1. See if there is another currency other than USD with a value in it that matches the activity.
2. If there is, look up its exchange rate lower down the page and multiply it (the exchange rate) by the figure found next to the currency in the first lookup table.
3. If there is no other currency with a value other than USD, return the USD value in the first lookup table.

Step 3 is the bit I am not sure I've interpreted correctly, as it doesn't seem to be catered for in your failed formula.

Excel 2016 (Windows) 32 bit
B
C
D
3
USD
Period
2
4
Activity
Curr.
Aug-17
5
BHP Project Execution
USD
29,696.80
6
BHP Direct Services
USD
473,229.40
7
Project Services - MOPS
USD
14,700.00
8
Projects
USD
47,000.00
9
Mech Maintenance
USD
22,050.00
10
Shipyard Project Team
USD
66,214.78
11
MMS Crew Indirect Costs
USD
253,515.75
12
BHPB Personnel Indirect Cost
USD
1,470.00
13
Contingency
USD
500,000.00
14
GRAND TOTAL
1,407,877
 Sheet: Sheet1  Reply With Quote

3. Formula Query 1.xlsx

OK I've entered the 1st 3. Pls let me know if you need all the yellow cells populated. I didn't use calculator as someone has taken it from my desk! Thanks...  Reply With Quote

4. There's a calculator on your PC!!! Do you think I was born yesterday? See post #12 above and the attachment.  Reply With Quote

5. OK - let me have another look.

OK - I understand. Give me five minutes to tweak my formula.  Reply With Quote

6. OK. It's an array formula (C+S+E as before):

=INDEX(\$D\$19:\$D\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63=C5),0))+INDEX(\$D\$19:\$D\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63="AUD"),0))*INDEX(\$D\$67:\$D\$74,MATCH("AUD",\$C\$67:\$C\$74,0))+INDEX(\$D\$19:\$D\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63="NOK"),0))*INDEX(\$D\$67:\$D\$74,MATCH("NOK",\$C\$67:\$C\$74,0))+INDEX(\$D\$19:\$D\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63="EUR"),0))*INDEX(\$D\$67:\$D\$74,MATCH("EUR",\$C\$67:\$C\$74,0))+INDEX(\$D\$19:\$D\$63,MATCH(1,(\$B\$19:\$B\$63=B5)*(\$C\$19:\$C\$63="SGD"),0))*INDEX(\$D\$67:\$D\$74,MATCH("SGD",\$C\$67:\$C\$74,0))  Reply With Quote

7. And here's a version that you can enter into D5 and drag copy across to the other months and down:

=INDEX(D\$19: D\$63,MATCH(1,(\$B\$19:\$B\$63=\$B5)*(\$C\$19:\$C\$63=\$C5),0))+INDEX(D\$19: D\$63,MATCH(1,(\$B\$19:\$B\$63=\$B5)*(\$C\$19:\$C\$63="AUD"),0))*INDEX(D\$67: D\$74,MATCH("AUD",\$C\$67:\$C\$74,0))+INDEX(D\$19: D\$63,MATCH(1,(\$B\$19:\$B\$63=\$B5)*(\$C\$19:\$C\$63="NOK"),0))*INDEX(D\$67: D\$74,MATCH("NOK",\$C\$67:\$C\$74,0))+INDEX(D\$19: D\$63,MATCH(1,(\$B\$19:\$B\$63=\$B5)*(\$C\$19:\$C\$63="EUR"),0))*INDEX(D\$67: D\$74,MATCH("EUR",\$C\$67:\$C\$74,0))+INDEX(D\$19: D\$63,MATCH(1,(\$B\$19:\$B\$63=\$B5)*(\$C\$19:\$C\$63="SGD"),0))*INDEX(D\$67: D\$74,MATCH("SGD",\$C\$67:\$C\$74,0))  Reply With Quote

8. Am I to assume that you have tried the formula and it works for you? Please give some feedback. Thanks.  Reply With Quote

9. Wow!   Reply With Quote

10. That works thank you!!! Sorry for late reply but I finished work Friday before I could test it, just back this morning. I am in different time-zone - Australia. thanks again!  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•