XLOOKUP Issue

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
334
Office Version
  1. 2013
Platform
  1. Windows
I use this workbook for collecting and maintaining a listing of Fortissimo Exchange Rates per the dates shown. I base my use of Fortissimo on my annual user account with the fiscal year beginning April 1 and ending March 31 of the following year. This you will see in the attached Xl2bb Mini Sheet.
The sheet CZK2USD only shows the dates beginning May 8, which is the date I began using this sheet to collect the data.
You will notice that the dollar amount columns, headed with the name of the month, e.g. April are relevant to the rate columns, labeled with the month and year, e.g. Apr 2023.
You will also notice I have included some minimum & maximum rates, dollar amounts, and associated dates.
You will also notice that the dollar amounts are based on an exchange of 10,000 CZK, which is much different from the file from which this bogus file was created. In addition, all rate & date data is copied from a helper file.
What I have been struggling with is after having established the current month and from that single piece of data, i.e. the current month, then get the minimum & maximum rates and dollar amounts from the column associated with the current month, along with the associated dates of these minimums and maximums.
I have tried several different approaches using the following as search terms and from these searches tried several different formulas but to no avail.
🔴 how to get the Current month from a list of months
🔴 lookup a value to find in a row range
🔴 get a value from a column range that matches a column label
🔴 return column location if statement true

Any help in the right direction would be much appreciated.

BogusCZK2USD.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1Minimum Values ððð21.479$465.5721.483$465.4821.487$465.4021.491$465.3121.496$465.2021.500$465.1221.500$465.1221.500$465.1221.500$465.1221.500$465.1221.500$465.1221.500$465.12242525A ColCntMinimum Rate ððð20.841Date of Minimum Rate ðððMon-08 May 2023 > 07:10:58
2Maximum Values ððð20.841$479.8220.845$479.7320.849$479.6420.853$479.5520.858$479.4320.862$479.3420.862$479.3420.862$479.3420.862$479.3420.862$479.3420.862$479.3420.862$479.34$A$2524Maximum Rate ððð21.479Date of Maximum Rate ðððWed-24 May 2023 > 07:16:08
3CZK2USD202320235/24/2023Date of Minimum $s Per Apr 2023 ðððWed-24 May 2023 > 07:16:08
4DateApr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar 20242024Date of Maximum $s Per Apr 2023 ðððMon-08 May 2023 > 07:10:58
5DateAprMayJunJulAugSepOctNovDecJanFebMarCurrent Month ððð5May 2023
6DateAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch10,000.00 KčThe lower the rate the higher the dollars#VALUE!
7DateApr 2023May 2023Jun 2023Jul 2023Aug 2023Sep 2023Oct 2023Nov 2023Dec 2023Jan 2024Feb 2024Mar 2024TRUE$C$3
8Mon-08 May 2023 > 07:10:5820.841$479.8220.845$479.7320.849$479.6420.853$479.5520.858$479.4320.862$479.3420.862$479.3420.862$479.3420.862$479.3420.862$479.3420.862$479.3420.862$479.34
9Tue-09 May 2023 > 09:02:5520.860$479.3920.864$479.2920.868$479.2020.873$479.0920.877$479.0020.881$478.9020.881$478.9020.881$478.9020.881$478.9020.881$478.9020.881$478.9020.881$478.90#N/AMay 2023
10Wed-10 May 2023 > 08:23:4220.860$479.3920.864$479.2920.868$479.2020.873$479.0920.877$479.0020.881$478.9020.881$478.9020.881$478.9020.881$478.9020.881$478.9020.881$478.9020.881$478.90Jun 2023
11Thu-11 May 2023 > 07:23:3220.928$477.8320.933$477.7120.937$477.6220.941$477.5320.945$477.4420.949$477.3520.949$477.3520.949$477.3520.949$477.3520.949$477.3520.949$477.3520.949$477.35Jul 2023
12Fri-12 May 2023 > 07:48:1121.036$475.3821.040$475.2921.044$475.1921.048$475.1021.053$474.9921.057$474.9021.057$474.9021.057$474.9021.057$474.9021.057$474.9021.057$474.9021.057$474.90Aug 2023
13Sat-13 May 2023 > 07:38:3121.133$473.1921.137$473.1021.142$472.9921.146$472.9021.150$472.8121.154$472.7221.154$472.7221.154$472.7221.154$472.7221.154$472.7221.154$472.7221.154$472.72Sep 2023
14Sun-14 May 2023 > 07:37:2721.133$473.1921.137$473.1021.142$472.9921.146$472.9021.150$472.8121.154$472.7221.154$472.7221.154$472.7221.154$472.7221.154$472.7221.154$472.7221.154$472.72Oct 2023
15Mon-15 May 2023 > 07:32:1821.182$472.1021.187$471.9921.191$471.9021.195$471.8121.199$471.7221.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.63Nov 2023
16Tue-16 May 2023 > 07:26:5721.182$472.1021.187$471.9921.191$471.9021.195$471.8121.199$471.7221.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.63Dec 2023
17Wed-17 May 2023 > 06:31:2821.182$472.1021.187$471.9921.191$471.9021.195$471.8121.199$471.7221.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.6321.203$471.63Jan 2024
18Thu-18 May 2023 > 07:14:3121.299$469.5121.303$469.4221.307$469.3321.312$469.2221.316$469.1321.320$469.0421.320$469.0421.320$469.0421.320$469.0421.320$469.0421.320$469.0421.320$469.04Feb 2024
19Fri-19 May 2023 > 07:40:1821.328$468.8721.332$468.7821.336$468.6921.341$468.5821.345$468.4921.349$468.4121.349$468.4121.349$468.4121.349$468.4121.349$468.4121.349$468.4121.349$468.41Mar 2024
20Sat-20 May 2023 > 07:53:0521.475$465.6621.479$465.5721.483$465.4821.487$465.4021.492$465.2921.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.20
21Sun-21 May 2023 > 07:29:2221.475$465.6621.479$465.5721.483$465.4821.487$465.4021.492$465.2921.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.20
22Mon-22 May 2023 > 07:14:4821.475$465.6621.479$465.5721.483$465.4821.487$465.4021.492$465.2921.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.2021.496$465.20
23Tue-23 May 2023 > 07:16:5521.425$466.7421.429$466.6621.433$466.5721.437$466.4821.442$466.3721.446$466.2921.446$466.2921.446$466.2921.446$466.2921.446$466.2921.446$466.2921.446$466.29
24Wed-24 May 2023 > 07:16:0821.479$465.5721.483$465.4821.487$465.4021.491$465.3121.496$465.2021.500$465.1221.500$465.1221.500$465.1221.500$465.1221.500$465.1221.500$465.1221.500$465.12
CZK2USD
Cell Formulas
RangeFormula
B1,X1,V1,T1,R1,P1,N1,L1,J1,H1,F1,D1B1=XLOOKUP(C1,C8:C24,B8:B24,0)
C1,Y1,W1,U1,S1,Q1,O1,M1,K1,I1,G1,E1C1=MIN(C8:C24)
B2,X2,V2,T2,R2,P2,N2,L2,J2,H2,F2,D2B2=XLOOKUP(C2,C8:C24,B8:B24,0)
C2,Y2,W2,U2,S2,Q2,O2,M2,K2,I2,G2,E2C2=MAX(C8:C24)
AA1AA1=EXTRACTNUMBERS(AD2,TRUE)
AB1AB1=SUM(AA1+1)
AC1AC1=EXTRACTNUMBERS(AB1)
AC2AC2=ADDRESS(AC1,1)
AD2AD2=COUNTA(A:A)
AG1AG1=MIN($B$8:$B$24)
AG2AG2=MAX($B$8:$B$24)
A3A3=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))
AD3,AF3AD3=TODAY()
AE3:AE4AE3=TEXT(AD3,"yyyy")
AD4AD4=EDATE(AD3,12)
C4,Y4,W4,U4,S4,Q4,O4,M4,K4,I4,G4,E4C4=TEXT(C5,"mmm ")
C5,Y5,W5,U5,S5,Q5,O5,M5,K5,I5,G5,E5C5=C6
AJ5AJ5=TEXT(AF3,"mmm yyyy")
AI1AI1=XLOOKUP($AG$1,$B$8:$B$24,$A$8:$A$24,0)
AI2AI2=XLOOKUP($AG$2,$B$8:$B$24,$A$8:$A$24,0)
AI3AI3=XLOOKUP($C$1,$C$8:$C$24,$A$8:$A$24,0)
AI4AI4=XLOOKUP($C$2,$C$8:$C$24,$A$8:$A$24,0)
AI5AI5=MONTH(AF3)
AI6AI6=XLOOKUP(AJ5,AJ8:AJ19,B7:X7,0)
B7,R7,P7,N7,L7,J7,H7,F7,D7B7=C4&$AE$3
T7,X7,V7T7=U4&$AE$4
AH7AH7=ISNUMBER(MATCH(AJ5,B7:X7,0))
AI7AI7=ADDRESS(MATCH(AJ5,B7:X7,0),3)
AH9AH9=INDEX(B8:Y24,MATCH(AJ5,B7:X7,1),MATCH(AJ5,B8:Y24,1))
AJ9AJ9=$D$7
AJ10AJ10=$F$7
AJ11AJ11=$H$7
AJ12AJ12=$J$7
AJ13AJ13=$L$7
AJ14AJ14=$N$7
AJ15AJ15=$P$7
AJ16AJ16=$R$7
AJ17AJ17=$T$7
AJ18AJ18=$V$7
AJ19AJ19=$X$7
Y8:Y24,W8:W24,U8:U24,S8:S24,Q8:Q24,O8:O24,M8:M24,K8:K24,I8:I24,G8:G24,E8:E24,C8:C24C8=SUM($AF$6/B8)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
A few comments:
  1. In your columns C, E, G, etc. (where the exchange rate is applied), you do not need the SUM function. A simple division is sufficient.
  2. I do not understand the need for columns C, E, G, etc., as those values are derived from the exchange rates in columns B, D, F, etc., and the exchange basis (10,000 CZK in this example) seems to be fixed, so you could calculate these values anywhere, if and when they are needed.
  3. Finding the MAX or MIN of the exchange rates occurs on the same rows as the MAX and MIN U.S. Dollar values, therefore, I would consider consolidating the table by eliminating nearly half the columns (as shown in example below).
  4. I am assuming that in practice, you will want to use dates other than "TODAY", because you do not know the exchange rates in the future...do you? This point aside, given some date in cell AF3 (or Q2 in my consolidated example below), your approach for the month number and text-based description of the month-year in cells AI5 and AJ5 is fine, but these do not need to be done explicitly. And rather than convert dates to some type of text format, it is more convenient to leave them in numeric form (done in the example below).
  5. Your question centers around the use of XLOOKUP, but it appears that your account details have not been updated to your current version (probably Excel 365?).
  6. I do not understand the connection between column A and the rest of the table to the right. Column A appears to provide dates and times for a particular month (part of May in this case), but those dates are completely irrelevant if you want information about the max and min exchange rates for April (in the example below, you will see this apparent nonsensical result when I overwrite the TODAY() function and query the table for April). Is column A intended to have day values of 1 through 31 that would apply to all months shown to the right (and some of those lower rows would be unused for months having fewer than 31 days)?
MrExcel_20230523.xlsx
ABCDEFGHIJKLMNOPQR
1CZK2USDFiscal Year2023
2Date for Lookups4/15/2023
3Month Beginning4/1/20235/1/20236/1/20237/1/20238/1/20239/1/202310/1/202311/1/202312/1/20231/1/20242/1/20243/1/2024Exchange Amt (CZK)10,000
4DateApr 2023May 2023Jun 2023Jul 2023Aug 2023Sep 2023Oct 2023Nov 2023Dec 2023Jan 2024Feb 2024Mar 2024
5Mon-08 May 2023 > 07:10:5820.84120.84520.84920.85320.85820.86220.86220.86220.86220.86220.86220.862RateAmt USDDate of Occurrence
6Tue-09 May 2023 > 09:02:5520.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881Max21.479$465.57Wed-24 May 2023 > 07:16:08
7Wed-10 May 2023 > 08:23:4220.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881Min20.841$479.82Mon-08 May 2023 > 07:10:58
8Thu-11 May 2023 > 07:23:3220.92820.93320.93720.94120.94520.94920.94920.94920.94920.94920.94920.949
9Fri-12 May 2023 > 07:48:1121.03621.04021.04421.04821.05321.05721.05721.05721.05721.05721.05721.057
10Sat-13 May 2023 > 07:38:3121.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
11Sun-14 May 2023 > 07:37:2721.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
12Mon-15 May 2023 > 07:32:1821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
13Tue-16 May 2023 > 07:26:5721.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
14Wed-17 May 2023 > 06:31:2821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
15Thu-18 May 2023 > 07:14:3121.29921.30321.30721.31221.31621.32021.32021.32021.32021.32021.32021.320
16Fri-19 May 2023 > 07:40:1821.32821.33221.33621.34121.34521.34921.34921.34921.34921.34921.34921.349
17Sat-20 May 2023 > 07:53:0521.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
18Sun-21 May 2023 > 07:29:2221.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
19Mon-22 May 2023 > 07:14:4821.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
20Tue-23 May 2023 > 07:16:5521.42521.42921.43321.43721.44221.44621.44621.44621.44621.44621.44621.446
21Wed-24 May 2023 > 07:16:0821.47921.48321.48721.49121.49621.50021.50021.50021.50021.50021.50021.500
CZK2USD
Cell Formulas
RangeFormula
A1A1=TEXTAFTER(CELL("filename"),"]")
B3:M3B3=EDATE(DATE($Q$1,4,1),SEQUENCE(,12,0))
B4:M4B4=TEXT(B3,"mmm yyyy")
P6P6=MAX(FILTER($B$5:$M$40,$B$3:$M$3=EOMONTH($Q$2,-1)+1))
Q6:Q7Q6=$Q$3/$P6
R6:R7R6=XLOOKUP($P6,FILTER($B$5:$M$40,$B$3:$M$3=EOMONTH($Q$2,-1)+1),$A$5:$A$40)
P7P7=LET(ary,FILTER($B$5:$M$40,$B$3:$M$3=EOMONTH($Q$2,-1)+1),MIN(FILTER(ary,ary<>0)))
Dynamic array formulas.
 
Upvote 0
Thanks, KRice.
I am working now with your suggestions, but the sequence function does not work in Excel 2013.
So if any of the above formulas you suggest are not available for me in Excel 2013 perhaps you have alternatives?
Here then is an XL2bb Mini Sheet of my attempt at your suggestions.

BogusCZK2USD2PerMrExcel.xlsx
ABCDEFGHIJKLMNOPQR
1CZK2USDFiscal Year2023
2Date for Lookups4/15/2023
3Month Beginning#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?Exchange Amt (CZK)10000
4Date#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
5Mon-08 May 2023 > 07:10:5820.84120.84520.84920.85320.85820.86220.86220.86220.86220.86220.86220.862RateAmt USDDate of Occurrence
6Tue-09 May 2023 > 09:02:5520.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881MaxThe filter function does not work in Excel 2023
7Wed-10 May 2023 > 08:23:4220.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881MinThe filter function does not work in Excel 2023
8Thu-11 May 2023 > 07:23:3220.92820.93320.93720.94120.94520.94920.94920.94920.94920.94920.94920.949
9Fri-12 May 2023 > 07:48:1121.03621.04021.04421.04821.05321.05721.05721.05721.05721.05721.05721.057
10Sat-13 May 2023 > 07:38:3121.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
11Sun-14 May 2023 > 07:37:2721.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
12Mon-15 May 2023 > 07:32:1821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
13Tue-16 May 2023 > 07:26:5721.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
14Wed-17 May 2023 > 06:31:2821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
15Thu-18 May 2023 > 07:14:3121.29921.30321.30721.31221.31621.32021.32021.32021.32021.32021.32021.320
16Fri-19 May 2023 > 07:40:1821.32821.33221.33621.34121.34521.34921.34921.34921.34921.34921.34921.349
17Sat-20 May 2023 > 07:53:0521.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
18Sun-21 May 2023 > 07:29:2221.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
19Mon-22 May 2023 > 07:14:4821.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
20Tue-23 May 2023 > 07:16:5521.42521.42921.43321.43721.44221.44621.44621.44621.44621.44621.44621.446
21Wed-24 May 2023 > 07:16:0821.47921.48321.48721.49121.49621.50021.50021.50021.50021.50021.50021.500
CZK2USD
Cell Formulas
RangeFormula
A1A1=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-SEARCH("]",CELL("filename",A2)))
B3:M3B3=EDATE(DATE($Q$1,4,1),SEQUENCE(,12,0))
B4:M4B4=TEXT(B3,"mmm yyyy")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
That was the point underlying my comment #5 earlier. You are asking about XLOOKUP, but that function only became available in Excel 365 and onward. It is not available natively in Excel 2013. Did you install an add-in to provide that functionality? Please don't try to adopt the version I posted above as many more formulas use functions that are not native. I'll repost another version soon that uses native functions for Excel 2013.
 
Upvote 0
I did install the XLOOKUP addin, been using it for several years. So if anything you post is better suited to using XLOOKUP then for sure I will be able to use it.
No hurry. I have a lot of time on my hands.
 
Upvote 0
Thank you for the clarification. Here is a version using functions native to Excel 2013. Let me know if this returns expected values...and please see my other comments posted previously. I am still not clear about column A and how it seemingly does not relate to 11 of the monthly columns to its right.
MrExcel_20230523.xlsx
ABCDEFGHIJKLMNOPQR
1CZK2USDFiscal Year2023
2Date for Lookups4/15/2023
3Month Beginning4/1/20235/1/20236/1/20237/1/20238/1/20239/1/202310/1/202311/1/202312/1/20231/1/20242/1/20243/1/2024Exchange Amt (CZK)10,000
4DateApr 2023May 2023Jun 2023Jul 2023Aug 2023Sep 2023Oct 2023Nov 2023Dec 2023Jan 2024Feb 2024Mar 2024
5Mon-08 May 2023 > 07:10:5820.84120.84520.84920.85320.85820.86220.86220.86220.86220.86220.86220.862RateAmt USDDate of Occurrence
6Tue-09 May 2023 > 09:02:5520.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881Max21.479$465.57Wed-24 May 2023 > 07:16:08
7Wed-10 May 2023 > 08:23:4220.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881Min20.841$479.82Mon-08 May 2023 > 07:10:58
8Thu-11 May 2023 > 07:23:3220.92820.93320.93720.94120.94520.94920.94920.94920.94920.94920.94920.949
9Fri-12 May 2023 > 07:48:1121.03621.04021.04421.04821.05321.05721.05721.05721.05721.05721.05721.057
10Sat-13 May 2023 > 07:38:3121.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
11Sun-14 May 2023 > 07:37:2721.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
12Mon-15 May 2023 > 07:32:1821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
13Tue-16 May 2023 > 07:26:5721.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
14Wed-17 May 2023 > 06:31:2821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
15Thu-18 May 2023 > 07:14:3121.29921.30321.30721.31221.31621.32021.32021.32021.32021.32021.32021.320
16Fri-19 May 2023 > 07:40:1821.32821.33221.33621.34121.34521.34921.34921.34921.34921.34921.34921.349
17Sat-20 May 2023 > 07:53:0521.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
18Sun-21 May 2023 > 07:29:2221.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
19Mon-22 May 2023 > 07:14:4821.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
20Tue-23 May 2023 > 07:16:5521.42521.42921.43321.43721.44221.44621.44621.44621.44621.44621.44621.446
21Wed-24 May 2023 > 07:16:0821.47921.48321.48721.49121.49621.50021.50021.50021.50021.50021.50021.500
CZK2USD
Cell Formulas
RangeFormula
A1A1=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
B3:M3B3=EDATE(DATE($Q$1,3,1),COLUMNS($B:B))
B4:M4B4=TEXT(B3,"mmm yyyy")
P6P6=MAX(INDEX($B$5:$M$40,,MATCH(EOMONTH($Q$2,-1)+1,$B$3:$M$3,0)))
Q6:Q7Q6=$Q$3/$P6
R6:R7R6=INDEX($A$5:$A$40,MATCH($P6,INDEX($B$5:$M$40,,MATCH(EOMONTH($Q$2,-1)+1,$B$3:$M$3,0)),0))
P7P7=MIN(INDEX($B$5:$M$40,,MATCH(EOMONTH($Q$2,-1)+1,$B$3:$M$3,0)))
 
Upvote 0
Awesome! Works great. I changed cell P2 to read "Month for Lookups" and the format in cell Q2 to be the current month.
Thank you!!!

BogusCZK2USD2PerMrExcel.xlsx
ABCDEFGHIJKLMNOPQR
1CZK2USDFiscal Year2023
2Month for LookupsMay
3Month Beginning4/1/20235/1/20236/1/20237/1/20238/1/20239/1/202310/1/202311/1/202312/1/20231/1/20242/1/20243/1/2024Exchange Amt (CZK)10000
4DateApr 2023May 2023Jun 2023Jul 2023Aug 2023Sep 2023Oct 2023Nov 2023Dec 2023Jan 2024Feb 2024Mar 2024
5Mon-08 May 2023 > 07:10:5820.84120.84520.84920.85320.85820.86220.86220.86220.86220.86220.86220.862RateAmt USDDate & Time of Occurrence
6Tue-09 May 2023 > 09:02:5520.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881Max21.483$465.48Wed-24 May 2023 > 07:16:08
7Wed-10 May 2023 > 08:23:4220.86020.86420.86820.87320.87720.88120.88120.88120.88120.88120.88120.881Min20.845$479.73Mon-08 May 2023 > 07:10:58
8Thu-11 May 2023 > 07:23:3220.92820.93320.93720.94120.94520.94920.94920.94920.94920.94920.94920.949
9Fri-12 May 2023 > 07:48:1121.03621.04021.04421.04821.05321.05721.05721.05721.05721.05721.05721.057
10Sat-13 May 2023 > 07:38:3121.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
11Sun-14 May 2023 > 07:37:2721.13321.13721.14221.14621.15021.15421.15421.15421.15421.15421.15421.154
12Mon-15 May 2023 > 07:32:1821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
13Tue-16 May 2023 > 07:26:5721.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
14Wed-17 May 2023 > 06:31:2821.18221.18721.19121.19521.19921.20321.20321.20321.20321.20321.20321.203
15Thu-18 May 2023 > 07:14:3121.29921.30321.30721.31221.31621.32021.32021.32021.32021.32021.32021.320
16Fri-19 May 2023 > 07:40:1821.32821.33221.33621.34121.34521.34921.34921.34921.34921.34921.34921.349
17Sat-20 May 2023 > 07:53:0521.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
18Sun-21 May 2023 > 07:29:2221.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
19Mon-22 May 2023 > 07:14:4821.47521.47921.48321.48721.49221.49621.49621.49621.49621.49621.49621.496
20Tue-23 May 2023 > 07:16:5521.42521.42921.43321.43721.44221.44621.44621.44621.44621.44621.44621.446
21Wed-24 May 2023 > 07:16:0821.47921.48321.48721.49121.49621.50021.50021.50021.50021.50021.50021.500
CZK2USD
Cell Formulas
RangeFormula
A1A1=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
B3:M3B3=EDATE(DATE($Q$1,3,1),COLUMNS($B:B))
B4:M4B4=TEXT(B3,"mmm yyyy")
P6P6=MAX(INDEX($B$5:$M$40,,MATCH(EOMONTH($Q$2,-1)+1,$B$3:$M$3,0)))
Q6:Q7Q6=$Q$3/$P6
R6:R7R6=INDEX($A$5:$A$40,MATCH($P6,INDEX($B$5:$M$40,,MATCH(EOMONTH($Q$2,-1)+1,$B$3:$M$3,0)),0))
P7P7=MIN(INDEX($B$5:$M$40,,MATCH(EOMONTH($Q$2,-1)+1,$B$3:$M$3,0)))
 
Upvote 0
That's good to hear...and I'm happy to help. I don't see any issue changing the display format for Q2, but the content of that cell still needs to be a complete date, as the EOMONTH function uses the date to determine the end of the prior month. And then 1 day is added to the end of that month to determine the beginning of the queried month, which is then used by MATCH to find the correct column in your exchange rate table.
 
Upvote 0
Again thank you for your expertise. I was elated to get this working. If it never worked it would not have been a big deal in the grand scheme of what data I was working with, but elated regardless.
I have many different types of data scattered throughout many files and what you see here in this post is my meager attempt at consolidation, correlation, history tracking, and data query. I am not much interested in having all these various types of data in one file or a limited number of files as this might create more problems than I care to deal with. Besides, at 73 years old and retired I am not at the very least very busy so this all gives me something to do, not to mention Excel is my favorite program and I just simply love working with it.
To put it another way, annually I am working with about 30 separate files to keep track of all kinds of things too numerous to list. Helps keep this 73-year-old brain from going to dust.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top