MATCH MIN INDIRECT ADDRESS

[xoxe1337]

Hello, I would really appreciate if somebody could assist with the problem.

=IFERROR(MATCH(MIN(INDIRECT("'Export Data'!"&ADDRESS(
MATCH(I2,'Export Data'!$A:$A,0),51,4)&":"&ADDRESS(
MATCH(I3,'Export Data'!$A:$A,0),51,4))),'Export Data'!AY:AY,0)-2,"")

I2 and I3 specify row range numbers
51 is column same as AY:AY
I wanted the formula to check the values of cells(numbers) between row numbers in range specified in I2 and I3 cells and return minimum row number value.
The formula is working fine unless AY:AY has repetitive value then it will ignore I2 I3 range and return the first repetitive number.
If i want to find the smallest value in range of row numbers 45-57, the formula will ignore my specified range and return me the row 35, just because row 35 has the same numeric value as row 49 which is in range 45-57.

 

[StephenCrump]

Welcome to the Forum!

A bit of a guess ....

You say I2 and I3 are row numbers, and your formula takes off 2 at the end, so I am guessing that 'Export Data' looks like this:

	A																																																		AY	AZ
1
2
3	1																																																		99
4	2																																																		1
5	3																																																		99
6	4																																																		99
7	5																																																		99
8	6																																																		99
9	7																																																		1	<--- Row 9
10	8																																																		99
11	9																																																		99
12	10																																																		99
13	11																																																		99
14	12																																																		99
15	etc
Export Data

In which case, in Excel 2021 you could do this:

	A	B	C	D	E	F	G	H	I	J	K
1									In: Rows		Out
2									5		9
3									8
Sheet1

Cell Formulas
Range	Formula
K2	K2	=LET(r,ROW('Export Data'!A3)-1,AY,'Export Data'!AY:AY,x,INDEX(AY,I2+r):INDEX(AY,I3+r),MATCH(MIN(x),x,)+I2+r-1)

But perhaps you need this instead?

	A																																																		AY	AZ
1
2
3	Blah																																																		99
4	Blah																																																		1
5	Blah																																																		99
6	Blah																																																		99
7	Start																																																		99
8	Blah																																																		99
9	Blah																																																		1	<--- Row 9
10	End																																																		99
11	Blah																																																		99
12	Blah																																																		99
13	Blah																																																		99
14	Blah																																																		99
15	etc
Export Data

	A	B	C	D	E	F	G	H	I	J	K
1									In: Rows		Out
2									Start		9
3									End
Sheet1

Cell Formulas
Range	Formula
K2	K2	=LET(A,'Export Data'!A:A,AY,'Export Data'!AY:AY,x,INDEX(AY,MATCH(I2,A,)):INDEX(AY,MATCH(I3,A,)),MATCH(MIN(x),x,)+MIN(ROW(x))-1)

 

[xoxe1337]

Thank you very much for your time and assistance. Sadly LET function does not work in that specific Excel version. But this one below worked perfectly:

=IFERROR(
MATCH(
MIN(
INDEX('Export Data'!AY:AY,MATCH(I2,'Export Data'!$A:$A,0))
:INDEX('Export Data'!AY:AY,MATCH(I3,'Export Data'!$A:$A,0))
),
INDEX('Export Data'!AY:AY,MATCH(I2,'Export Data'!$A:$A,0))
:INDEX('Export Data'!AY:AY,MATCH(I3,'Export Data'!$A:$A,0)),
0
)-3+MATCH(I2,'Export Data'!$A:$A,0),
""
)

 

[StephenCrump]

Great, I'm glad you got it working.