MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- Windows
I would like to get the value from the range N5:N16 that matches the month name in cell M23 based on the column range K5:K16.
I think I have the lookup column number incorrect. I set it to 1, but have tried 4 & 5 also.
Any help is greatly appreciated, thank you.
Is this possible? The attached XL2BB shows what I have tried so far without any success. I think I need to include some match function with the VLOOKUP that appears in cell M24.
I think I have the lookup column number incorrect. I set it to 1, but have tried 4 & 5 also.
Any help is greatly appreciated, thank you.
Is this possible? The attached XL2BB shows what I have tried so far without any success. I think I need to include some match function with the VLOOKUP that appears in cell M24.
FortissimoTabulation.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
K | L | M | N | O | P | |||
1 | Greyed Text Indicates Actual Rate Used & Kč Received | |||||||
2 | Fortissimo Exchange Based On $2,700 | |||||||
3 | ||||||||
4 | Month | Actual Rate | Dollars | Current Rates | Amt Tendered | Crowns Received | ||
5 | April | 25.026 | $2,000 | 22.047 | $2,000 | 50,052.00 Kč | ||
6 | May | 24.831 | $4,000 | 22.082 | $2,000 | 49,662.00 Kč | ||
7 | June | 23.558 | $8,200 | 22.100 | $4,200 | 98,943.60 Kč | ||
8 | July | 23.402 | $11,100 | 22.111 | $2,900 | 67,865.80 Kč | ||
9 | August | $13,800 | 22.121 | $2,700 | 59,726.70 Kč | |||
10 | September | $16,500 | 22.135 | $2,700 | 59,764.50 Kč | |||
11 | October | $19,200 | 22.135 | $2,700 | 59,764.50 Kč | |||
12 | November | $21,900 | 22.135 | $2,700 | 59,764.50 Kč | |||
13 | December | $24,600 | 22.135 | $2,700 | 59,764.50 Kč | |||
14 | January | $27,300 | 22.135 | $2,700 | 59,764.50 Kč | |||
15 | February | $30,000 | 22.135 | $2,700 | 59,764.50 Kč | |||
16 | March | $32,700 | 22.135 | $2,700 | 59,764.50 Kč | |||
17 | ||||||||
18 | Average: | 22.117 | ||||||
19 | ||||||||
20 | ||||||||
21 | Year | Month | Day | |||||
22 | 08/28/2020 | 2020 | 8 | 28 | ||||
23 | August | |||||||
24 | #VALUE! | |||||||
Current Rates |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N5 | N5 | =D2 |
M6:M16 | M6 | =SUM(M5+O6) |
N6 | N6 | =SUM(N5+U$7) |
N7 | N7 | =SUM(N6+V$7) |
N8 | N8 | =SUM(N7+W$7) |
N9 | N9 | =SUM(N8+X$7) |
N10 | N10 | =SUM(N9+Y$7) |
N11 | N11 | =SUM(N10+Z$7) |
N12 | N12 | =SUM(N11+AA$7) |
N13 | N13 | =SUM(N12+AB$7) |
N14 | N14 | =SUM(N13+AC$7) |
N15 | N15 | =SUM(N14+AD$7) |
N16 | N16 | =SUM(N15+AE$7) |
P5:P8 | P5 | =SUM(L5*O5) |
P9:P16 | P9 | =SUM(N9*O9) |
N18 | N18 | =AVERAGE(N5:N16) |
N22 | N22 | =YEAR(TODAY()) |
O22 | O22 | =MONTH(TODAY()) |
P22 | P22 | =DAY(TODAY()) |
M22 | M22 | =DATE(N22,O22,P22) |
M23 | M23 | =TEXT(DATE(O22,O22,1),"MMMM") |
M24 | M24 | =VLOOKUP(M23,K5:K16,1,N5:N16) |