If Exist in a Column Range

[MrDB4Excel]

I spent a great deal of time today looking through several options of VLOOKUP, HLOOKUP, XLOOKUP, and other Index and Match scenarios but could not seem to get things working correctly. I always ended with the result being either from the same row or of the type #VALUE! error.
If exist in a column range then copy from a row range the values in cells adjacent to the cell in which the value from the column range was found.

For example, in column “C” I need to find, if it exists, a value, in this case, this value will always be in a number format in column “C” such that the number format is always two digits to the left of the decimal and three digits to the right of the decimal: 00.000.

If the number exists in column “C” then I need to get the values in the row to the right of the cell but one row down. So for example, I need to find in column “C”, the value 20.943 (in the attached Xl2bb Mini Sheet 20.943 is in cell C16). To the right of cell C16 and one row down (row 17), you see a series of numbers from column “D” through to column “N” and these numbers are always of the format one digit to the left of the decimal and three digits to the right of the decimal: 0.000.

So then, if the number searched for does exist, then the formula would get the values in cells D-row# through to and including N-row#; in the example described above it would be D17 to N17. At this point is a simple matter of me copying these numbers to the other sheet called ForTab in the location beginning at cell N6 and thus would paste through to X6.

How would the formula know what number to see if it exists in column “C”? Good question. The simple answer is the formula looks at cell AJ2 on sheet DailyRate1Apr2023-31Mar2024. AJ2 is equal to cell L17 on sheet ForTab.

To make this a bit more tricky I would want the basic if exist formula to exist on sheet ForTab, thus picking it would either jump to sheet DailyRate1Apr2023-31Mar2024 and in the same breath get the cells D-row# through to and including N-row#; from which I would, of course, do a simple copy and paste back to ForTab—or it would give a message something akin to value does not exist. At this point, I would quickly know to run the numbers on Fortissimo’s website to get exchange rate values.

Side note: this workbook, BogusFortissimoTabulation2023.xlsx, was set up with bogus data but essentially is the same as the original workbook FortissimoTabulation2023.xlsx except the original has confidential data and several more sheets. Also, note that I have the XLOOKUP addin I am using for my Excel 2013 thus XLOOKUP works very well.

BogusFortissimoTabulation2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
1	Beginning With April 1, 2023		$1,000	$2,000	$3,000	$4,000	$5,000	$6,000	$7,000	$8,000	$9,000	$10,000	$11,000	$12,000					Extra Dates Per Specific Rate		16	18	18	B Col Count	Thu-01 June 2023	#N/A	1st Date/Time	2nd Date/Time	3rd Date/Time	=TEXT(??	=TEXT(??	=TEXT(??
2			April 2023	May 2023	June 2023	July 2023	August 2023	September 2023	October 2023	November 2023	December 2023	January 2024	February 2024	March 2024	Min ððð	64,923.30 Kč		3,100					$B$18	16	$O$16	16								#N/A		21.508	D	E	F	G	H	I	J	K	L	M	N
3															Max ððð	66,417.50 Kč		3,200						20	$O$6	6
4	7:47:58	Sat-01 April 2023	21.357	21.361	21.366	21.370	21.374	21.378	21.378	21.378	21.378	21.378	21.378	21.378	66,206.70 Kč	MDL	1	3,100	Apr 02, 2023 06:23:06 Apr 03, 2023 06:44:02	1							Apr 02, 2023 06:23:06	Apr 03, 2023 06:44:02		Apr 02, 2023 06:23:06	Apr 03, 2023 06:44:02		January	2023		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
5				0.004	0.005	0.004	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000			0																February	2024
6	5:59:11	Tue-04 April 2023	21.425	21.430	21.434	21.438	21.440	21.445	21.447	21.447	21.447	21.447	21.447	21.447	66,417.50 Kč	MDL	1	3,100	Feb 06, 2023 08:26:24 May 23, 2023 07:12:42	2							Feb 06, 2023 08:26:24	May 23, 2023 07:12:42		Feb 06, 2023 08:26:24	May 23, 2023 07:12:42		March
7				0.005	0.004	0.004	0.002	0.005	0.002	0.000	0.000	0.000	0.000	0.000			0																April
8	12:32:54	Wed-05 April 2023	21.162	21.167	21.171	21.175	21.179	21.183	21.183	21.183	21.183	21.183	21.183	21.183	65,602.20 Kč		1	3,100	Apr 06, 2023 07:29:06	3													May
9				0.005	0.004	0.004	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000			0																June
10	7:17:47	Tue-25 April 2023	21.075	21.079	21.083	21.088	21.092	21.096	21.096	21.096	21.096	21.096	21.096	21.096	65,332.50 Kč		0	3,100		13													#REF!
11				0.004	0.004	0.005	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000			0																January 2024
12	7:19:25	Wed-26 April 2023	20.948	20.953	20.957	20.961	20.965	20.969	20.969	20.969	20.969	20.969	20.969	20.969	64,938.80 Kč		1	3,100		14													February 2024
13				0.005	0.004	0.004	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000			0																March 2024
14	6:41:25	Thu-27 April 2023	21.007	21.011	21.015	21.019	21.023	21.028	21.028	21.028	21.028	21.028	21.028	21.028	65,121.70 Kč		1	3,100		15
15				0.004	0.004	0.004	0.004	0.005	0.000	0.000	0.000	0.000	0.000	0.000			0
16	7:51:15	Fri-28 April 2023	20.943	20.948	20.952	20.956	20.960	20.964	20.964	20.964	20.964	20.964	20.964	20.964	64,923.30 Kč		1	3,100		16
17				0.005	0.004	0.004	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000			0
DailyRate1Apr2023-31Mar2024

Cell Formulas
Range	Formula
C1	C1	=ForTab!$M$1
D1	D1	=ForTab!$N$1
E1	E1	=ForTab!$O$1
F1	F1	=ForTab!$P$1
G1	G1	=ForTab!$Q$1
H1	H1	=ForTab!$R$1
I1	I1	=ForTab!$S$1
J1	J1	=ForTab!$T$1
K1	K1	=ForTab!$U$1
L1	L1	=ForTab!$V$1
M1	M1	=ForTab!$W$1
N1	N1	=ForTab!$X$1
U1	U1	=EXTRACTNUMBERS(X2,TRUE)
V1	V1	=SUM(U1+2)
W1	W1	=EXTRACTNUMBERS(V1)
Z1	Z1	=XLOOKUP(Y1,$B$4:$B$17,$C$4:$C$17,0)
W2	W2	=ADDRESS(W1,2)
X2	X2	=COUNTA(E:E)
Y2	Y2	=CELL("address",INDEX(O:O,MATCH(MIN(O:O),O:O,0)))
Z2:Z3	Z2	=RIGHT(Y2,LEN(Y2)-FIND(CHAR(160),SUBSTITUTE(Y2,"$",CHAR(160),2)))
Y3	Y3	=CELL("address",INDEX(O:O,MATCH(MAX(O:O),O:O,0)))
AH2	AH2	=CELL("Address",XLOOKUP($AJ$4,$C$4:$C$61,$D$4:$D$61))
AJ2	AJ2	=ForTab!L17
P2	P2	=MIN($O$4:$O$17)
P3	P3	=MAX($O$4:$O$17)
O4,O16,O14,O12,O10,O8,O6	O4	=IFERROR(IF(SUM(C4*R4)=0,"",(SUM(C4*R4))),"")
R4:R17	R4	=IF(YEAR(B4)=2023,$R$2,IF(YEAR(B4)=2024,$R$3,""))
S4,S6	S4	=AD4&CHAR(10)&AE4
AD6:AE6,AD4:AE4	AD4	=TEXT(AA4,"mmm dd, yyyy hh:mm:ss")
AJ4:AU4	AJ4	=XLOOKUP($AJ$2,$C$4:$C$61,$C$4:$N$61)
Q11:Q17,Q5:Q9	Q5	=MOD(ROWS(Q$2:Q3),2)
Q10	Q10	=MOD(ROWS(Q$2:Q9),2)
AG10	AG10	=#REF!&" "&$AH$4
AG11:AG13	AG11	=AG4&" "&$AH$5
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4	Expression	=$Q4=0	text	NO
C5:N6,D4:N4,A4:B6,A7:N17	Expression	=$Q4=0	text	NO

BogusFortissimoTabulation2023.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1			Month	Actual Rate	Dollars	Current Rates	Amt Tendered	Crowns Received		Time of Data	Date of Data	$1,000	$2,000	$3,000	$4,000	$5,000	$6,000	$7,000	$8,000	$9,000	$10,000	$11,000	$12,000
2	April	1	Apr-2023	0.000	$1,000	21.508	$1,000	0.00 Kč				Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024
3	May	2	May-2023	0.000	$2,000	21.512	$1,000	0.00 Kč				$1,000	$2,000	$3,000	$4,000	$5,000	$6,000	$7,000	$8,000	$9,000	$10,000	$11,000	$12,000	USD to CZK
4	June	3	Jun-2023	0.000	$3,000	21.516	$1,000																	21.9347000
5	July	4	Jul-2023	0.000	$4,000	21.521	$1,000			19:00:26	Fri-09 June 2023	21.508	21.512	21.516	21.521	21.525	21.529	21.529	21.529	21.529	21.529	21.529	21.529	#REF!
6	August	5	Aug-2023	0.000	$5,000	21.525	$1,000						0.004	0.004	0.005	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000
7	September	6	Sep-2023	0.000	$6,000	21.529	$1,000
8	October	7	Oct-2023	0.000	$7,000	21.529	$1,000
9	November	8	Nov-2023	0.000	$8,000	21.529	$1,000
10	December	9	Dec-2023	0.000	$9,000	21.529	$1,000
11	January	10	Jan-2024	0.000	$10,000	21.529	$1,000				Jun 09, 2023 19:00:26
12	February	11	Feb-2024	0.000	$11,000	21.529	$1,000
13	March	12	Mar-2024	0.000	$12,000	21.529	$1,000
14
15
16						Year	Month	Day
17					6/9/2023	2023	6	9	$1000	Base ð	21.508
18					June
19
20					21.516	Year	Month	Day
21					6/9/2023	2023	6	9				6/9/2023	19:00:26	Friday, June, 2023 19:00:26
22			2023		7/9/2023		7
23			2024		June						2021
24			2025		July						2022
25			Apr	Apr-2023	$1,000	21.508	$1,000	April			June
26			May	May-2023	$2,000	21.512	$1,000	May			6
27			Jun	Jun-2023	$3,000	21.516	$1,000	June
28			Jul	Jul-2023	$4,000	21.521	$1,000	July			July
29			Aug	Aug-2023	$5,000	21.525	$1,000	August			7
30			Sep	Sep-2023	$6,000	21.529	$1,000	September
31			Oct	Oct-2023	$7,000	21.529	$1,000	October
32			Nov	Nov-2023	$8,000	21.529	$1,000	November
33			Dec	Dec-2023	$9,000	21.529	$1,000	December
34			Jan	Jan-2024	$10,000	21.529	$1,000	January
35			Feb	Feb-2024	$11,000	21.529	$1,000	February
36			Mar	Mar-2024	$12,000	21.529	$1,000	March
ForTab

Cell Formulas
Range	Formula
M1	M1	=F2
N1	N1	=F3
O1	O1	=F4
P1	P1	=F5
Q1	Q1	=F6
R1	R1	=F7
S1	S1	=F8
T1	T1	=F9
U1	U1	=F10
V1	V1	=F11
W1	W1	=F12
X1	X1	=F13
M2	M2	=D2
N2	N2	=D3
O2	O2	=D4
P2	P2	=D5
Q2	Q2	=D6
R2	R2	=D7
S2	S2	=D8
T2	T2	=D9
U2	U2	=D10
V2	V2	=D11
W2	W2	=D12
X2	X2	=D13
M3	M3	=$F2
N3	N3	=$F3
O3	O3	=$F4
P3	P3	=$F5
Q3	Q3	=$F6
R3	R3	=$F7
S3	S3	=$F8
T3	T3	=$F9
U3	U3	=$F10
V3	V3	=$F11
W3	W3	=$F12
X3	X3	=$F13
F2,F25	F2	=H2
G2	G2	=L17
F26:F36,F3:F13	F3	=SUM(F2+H3)
G3	G3	=SUM(G2+N$6)
G4	G4	=SUM(G3+O$6)
G5	G5	=SUM(G4+P$6)
G6	G6	=SUM(G5+Q$6)
G7	G7	=SUM(G6+R$6)
G8	G8	=SUM(G7+S$6)
G9	G9	=SUM(G8+T$6)
G10	G10	=SUM(G9+U$6)
G11	G11	=SUM(G10+V$6)
G12	G12	=SUM(G11+W$6)
G13	G13	=SUM(G12+X$6)
I2:I3	I2	=SUM(E2*H2)
Y4	Y4	='F:\Finances\CSOB\2023\[CurrencyConversionRatesWebQuery2023.xlsm]Current Rates'!$J$12
K5	K5	=NOW()-TODAY()
L5	L5	=F17
M5	M5	=$G2
N5	N5	=$G3
O5	O5	=$G4
P5	P5	=$G5
Q5	Q5	=$G6
R5	R5	=$G7
S5	S5	=$G8
T5	T5	=$G9
U5	U5	=$G10
V5	V5	=$G11
W5	W5	=$G12
X5	X5	=$G13
Y5	Y5	=#REF!
L11,N21	L11	=NOW()
D2:D13	D2	=E25
G17,D22,G21	G17	=YEAR(TODAY())
H17,H21	H17	=MONTH(TODAY())
I17,I21	I17	=DAY(TODAY())
J17	J17	="$"&H2
F17,F21	F17	=DATE(G17,H17,I17)
F18	F18	=TEXT(DATE(H17,H17,1),"MMMM")
M21	M21	=TODAY()
O21	O21	=TEXT(M21,"dddd, mmmm, yyyy ")&TEXT(N21,"hh:mm:ss")
H22	H22	=MONTH(F22)
D23	D23	=YEAR(TODAY())+1
D24	D24	=YEAR(TODAY())+2
F20	F20	=VLOOKUP(F23,$B$2:$G$13,6,0)
F22	F22	=EDATE(F21,1)
F23:F24	F23	=TEXT(DATE(H21,H21,1),"MMMM")
E25:E33	E25	=D25&"-"&$D$22
G25:G36	G25	=G2
E34:E36	E34	=D34&"-"&$D$23
L24	L24	=DATE(YEAR($L$23)+1,MONTH($L$23),DAY($L$23))
L25	L25	=IF(L26=H22,F24,F23)
L26	L26	=IF(I17>10,H22,H21)
L28	L28	=IF(L29=H21,F23,F24)
L29	L29	=IF(I17<10,H22,H21)
J25:J36	J25	=IF(I2=0,"",I2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:X6	Cell Value	contains "Zero"	text	NO

 

[DanteAmor]

I put the formula in the "ForTab" sheet in cells from N6 to X6. I hope I understood...

Dante Amor
	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Date of Data	1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000
2		Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024
3		1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000
4
5	45086	20.943	20.948	20.952	20.956	20.96	20.964	20.964	20.964	20.964	20.964	20.964	20.964
6			0.005	0.004	0.004	0.004	0.004	0	0	0	0	0	0
7
8
9
10
11	09/06/2023 17:47
12
13
14
15
16
17	20.943
ForTab

Cell Formulas
Range	Formula
N6:X6	N6	=INDEX('DailyRate1Apr2023-31Mar2024'!$D$4:$N$17,MATCH($L$17,'DailyRate1Apr2023-31Mar2024'!$C$4:$C$17,0)+1,COLUMNS($N$5:N5))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[MrDB4Excel]

I put the formula in the "ForTab" sheet in cells from N6 to X6. I hope I understood...

Dante Amor
	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Date of Data	1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000
2		Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024
3		1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000
4
5	45086	20.943	20.948	20.952	20.956	20.96	20.964	20.964	20.964	20.964	20.964	20.964	20.964
6			0.005	0.004	0.004	0.004	0.004	0	0	0	0	0	0
7
8
9
10
11	09/06/2023 17:47
12
13
14
15
16
17	20.943
ForTab

Cell Formulas
Range	Formula
N6:X6	N6	=INDEX('DailyRate1Apr2023-31Mar2024'!$D$4:$N$17,MATCH($L$17,'DailyRate1Apr2023-31Mar2024'!$C$4:$C$17,0)+1,COLUMNS($N$5:N5))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hello Dante,
It gives me the #N/A error.
Yes I understood to put your formula in the ForTab sheet from N6:X6

BogusFortissimoTabulation2023.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Current Rates	Amt Tendered	Crowns Received		Time of Data	Date of Data	$1,000	$2,000	$3,000	$4,000	$5,000	$6,000	$7,000	$8,000	$9,000	$10,000	$11,000	$12,000
2	21.943	$1,000	0.00 Kč				Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024
3	#N/A	$1,000	0.00 Kč				$1,000	$2,000	$3,000	$4,000	$5,000	$6,000	$7,000	$8,000	$9,000	$10,000	$11,000	$12,000
4	#N/A	$1,000
5	#N/A	$1,000			7:25:32	Sat-10 June 2023	21.943	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
6	#N/A	$1,000						#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
7	#N/A	$1,000
8	#N/A	$1,000
9	#N/A	$1,000
10	#N/A	$1,000
11	#N/A	$1,000				Jun 10, 2023 07:25:32
12	#N/A	$1,000
13	#N/A	$1,000
14
15
16	Year	Month	Day
17	2023	6	10	$1000	Base ð	21.943
ForTab

Cell Formulas
Range	Formula
M1	M1	=F2
N1	N1	=F3
O1	O1	=F4
P1	P1	=F5
Q1	Q1	=F6
R1	R1	=F7
S1	S1	=F8
T1	T1	=F9
U1	U1	=F10
V1	V1	=F11
W1	W1	=F12
X1	X1	=F13
M2	M2	=D2
N2	N2	=D3
O2	O2	=D4
P2	P2	=D5
Q2	Q2	=D6
R2	R2	=D7
S2	S2	=D8
T2	T2	=D9
U2	U2	=D10
V2	V2	=D11
W2	W2	=D12
X2	X2	=D13
M3	M3	=$F2
N3	N3	=$F3
O3	O3	=$F4
P3	P3	=$F5
Q3	Q3	=$F6
R3	R3	=$F7
S3	S3	=$F8
T3	T3	=$F9
U3	U3	=$F10
V3	V3	=$F11
W3	W3	=$F12
X3	X3	=$F13
I2:I3	I2	=SUM(E2*H2)
K5	K5	=NOW()-TODAY()
L5	L5	=F17
M5	M5	=$G2
N5	N5	=$G3
O5	O5	=$G4
P5	P5	=$G5
Q5	Q5	=$G6
R5	R5	=$G7
S5	S5	=$G8
T5	T5	=$G9
U5	U5	=$G10
V5	V5	=$G11
W5	W5	=$G12
X5	X5	=$G13
N6:X6	N6	=INDEX('DailyRate1Apr2023-31Mar2024'!$D$4:$N$17,MATCH($L$17,'DailyRate1Apr2023-31Mar2024'!$C$4:$C$17,0)+1,COLUMNS($N$5:N5))
L11	L11	=NOW()
G2	G2	=L17
G3	G3	=SUM(G2+N$6)
G4	G4	=SUM(G3+O$6)
G5	G5	=SUM(G4+P$6)
G6	G6	=SUM(G5+Q$6)
G7	G7	=SUM(G6+R$6)
G8	G8	=SUM(G7+S$6)
G9	G9	=SUM(G8+T$6)
G10	G10	=SUM(G9+U$6)
G11	G11	=SUM(G10+V$6)
G12	G12	=SUM(G11+W$6)
G13	G13	=SUM(G12+X$6)
G17	G17	=YEAR(TODAY())
H17	H17	=MONTH(TODAY())
I17	I17	=DAY(TODAY())
J17	J17	="$"&H2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:X6	Expression	=$Q6=0	text	NO

 

[DanteAmor]

It gives me the #N/A error.

the N/A error is because the value 21.943 does not exist in the sheet, try a value that does exist.
To avoid the error, use the IFERROR function.

 

[MrDB4Excel]

the N/A error is because the value 21.943 does not exist in the sheet, try a value that does exist.
To avoid the error, use the IFERROR function.

I figured out what I did wrong. I entered 21.943 in cell L17 on sheet ForTab by mistake when I meant to enter 20.943. Now it works.
Kudos to Dante. Now I will see how it works in the non-bogus workbook and will let you how that goes.
Many thanks thus far. Stay tuned, will be back shortly with an update on the other workbook.

 

[MrDB4Excel]

the N/A error is because the value 21.943 does not exist in the sheet, try a value that does exist.
To avoid the error, use the IFERROR function.

Now this is working very well in the non-bogus workbook. And because I have another sheet similar to DailyRate1Apr2023-31Mar2024 called DailyRate1Apr2022-31Mar2023 that was not shown in the bogus workbook I now have two rows that essentially are looking in their respective sheet for the intended match.

FortissimoTabulation2023.xlsx
	L	M	N	O	P	Q	R	S	T	U	V	W	X
6	DailyRate1Apr2023-31Mar2024 ððð		0.004	0.004	0.005	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000
7	DailyRate1Apr2022-31Mar2023 ððð		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
ForTab

Cell Formulas
Range	Formula
N6:X6	N6	=INDEX('DailyRate1Apr2023-31Mar2024'!$D$4:$N$555,MATCH($L$17,'DailyRate1Apr2023-31Mar2024'!$C$4:$C$555,0)+1,COLUMNS($N$5:N5))
N7:X7	N7	=INDEX('DailyRate1Apr2022-31Mar2023'!$D$4:$N$555,MATCH($L$17,'DailyRate1Apr2022-31Mar2023'!$C$4:$C$555,0)+1,COLUMNS($N$5:N6))

Unfortunately, this leads to another unforeseen issue.
Column "G" has formulas in it that give correct values depending on N6:X6.
Now that I have a new row for the other sheet how can I fix the formulas in column "G" to get the values in N7:X7 if the values in N6:X6 are #N/A due to the value in L17 is not found on sheet DailyRate1Apr2023-31Mar2024?
Likewise, if the value exists on both sheets DailyRate1Apr2023-31Mar2024 & DailyRate1Apr2022-31Mar2023 then there is not a problem.

FortissimoTabulation2023.xlsx
	G
1	Current Rates
2	21.508
3	21.512
4	21.516
5	21.521
6	21.525
7	21.529
8	21.529
9	21.529
10	21.529
11	21.529
12	21.529
13	21.529
ForTab

Cell Formulas
Range	Formula
G2	G2	=L17
G3	G3	=SUM(G2+N$6)
G4	G4	=SUM(G3+O$6)
G5	G5	=SUM(G4+P$6)
G6	G6	=SUM(G5+Q$6)
G7	G7	=SUM(G6+R$6)
G8	G8	=SUM(G7+S$6)
G9	G9	=SUM(G8+T$6)
G10	G10	=SUM(G9+U$6)
G11	G11	=SUM(G10+V$6)
G12	G12	=SUM(G11+W$6)
G13	G13	=SUM(G12+X$6)

 

[MrDB4Excel]

the N/A error is because the value 21.943 does not exist in the sheet, try a value that does exist.
To avoid the error, use the IFERROR function.

FYI The exchange website where I get the rates to input into my Excel workbook only gives the value for the input of dollar amount in the format 00.000. I use the values in N6:X6 to update the exchange rates shown in column "G"

Fortissimo - EXCHANGE RATES / CASH

www.devizy.cz

Too bad Fortissimo didn't have their site be more useful in regards to multiple currency input values instead of only one value at a time. If they did, and they allowed an input of a table from Excel that would be awesome.

 

[MrDB4Excel]

the N/A error is because the value 21.943 does not exist in the sheet, try a value that does exist.
To avoid the error, use the IFERROR function.

unforeseen issue?
I got this one figured out too. Obviously, if the searched-for value exists on both sheets then the values in for example N6 & N7 would be identical, thus the Max is going to be the same as the identical values in N6 & N7.

FortissimoTabulation2023.xlsx
	L	M	N	O	P	Q	R	S	T	U	V	W	X
6	DailyRate1Apr2023-31Mar2024 ððð		0.004	0.004	0.005	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000
7	DailyRate1Apr2022-31Mar2023 ððð
8			0.004	0.004	0.005	0.004	0.004	0.000	0.000	0.000	0.000	0.000	0.000
ForTab

Cell Formulas
Range	Formula
N6:X6	N6	=IFERROR(INDEX('DailyRate1Apr2023-31Mar2024'!$D$4:$N$555,MATCH($L$17,'DailyRate1Apr2023-31Mar2024'!$C$4:$C$555,0)+1,COLUMNS($N$5:N5)),"")
N7:X7	N7	=IFERROR(INDEX('DailyRate1Apr2022-31Mar2023'!$D$4:$N$555,MATCH($L$17,'DailyRate1Apr2022-31Mar2023'!$C$4:$C$555,0)+1,COLUMNS($N$5:N6)),"")
N8:X8	N8	=MAX(N6:N7)

FortissimoTabulation2023.xlsx
	G
2	21.508
3	21.512
4	21.516
5	21.521
6	21.525
7	21.529
8	21.529
9	21.529
10	21.529
11	21.529
12	21.529
13	21.529
ForTab

Cell Formulas
Range	Formula
G2	G2	=L17
G3	G3	=SUM(G2+N$8)
G4	G4	=SUM(G3+O$8)
G5	G5	=SUM(G4+P$8)
G6	G6	=SUM(G5+Q$8)
G7	G7	=SUM(G6+R$8)
G8	G8	=SUM(G7+S$8)
G9	G9	=SUM(G8+T$8)
G10	G10	=SUM(G9+U$8)
G11	G11	=SUM(G10+V$8)
G12	G12	=SUM(G11+W$8)
G13	G13	=SUM(G12+X$8)

 

[DanteAmor]

To avoid the error, use the IFERROR function.

Example:

=IFERROR(INDEX('DailyRate1Apr2023-31Mar2024'!$D$4:$N$17,MATCH($L$17,'DailyRate1Apr2023-31Mar2024'!$C$4:$C$17,0)+1,COLUMNS($N$5:N5)),"")