Combining Index Match Formula - REF error

[afinn211]

Hello,

I am using Office 365.

I am trying to combine the below Index Match formulas. Both work independently and return values, but the independent values don't do me any good because I need the intersection of the data, i.e., the place in the table where both values line up next to each other in their respective column. The problem is these values appear in other places, but in only in one place next to each other.

=INDEX(A6:A24806, MATCH(TRUE,(E6:E24806>=1720),0))
=INDEX(A6:A24806, MATCH(MIN(IF(F6:F24806>0,F6:F24806)),F6:F24806,0))

Below is my attempt at combining the formula, but I receive a REF error.
=INDEX(A6:A24806, MATCH(TRUE,(E6:E24806>=1720),0), MATCH(MIN(IF(F6:F24806>0,F6:F24806)),F6:F24806,0))

I think the reference error is due to the first Match formula returning 6380 and the second Match formula returning 12651. I think, if the formula was working correctly, then those numbers would be the same and it would be finding the intersection of the data in the table and then returning the corresponding value in column A.

Any help would be much appreciated!! Thanks.

Andy

 

[Anthony47]

Hummm
Are you sure that the array to fetch the data from is A6:A24806, i.e. a one column list?

 

[afinn211]

Yes, I need to locate a number in column A based on matching values in columns E and F.

 

[Peter_SSs]

Welcome to the MrExcel board!

Your requirement is not clear. Your first formula returns the value from column A in the first row where column E >= 1720. Column E can only first be >=1720 in (at most) one row so how can you be possibly looking for a value from another row based on something happening in column F?

What about making up a small set of dummy data for columns A, E & F where the required conditions are met within the first 10 or 15 rows and post that with XL2BB and explain again in relation to that sample data?

 

[afinn211]

My formulas are just my attempt to do what I need. So they aren’t correct.

What I need is the smallest value greater than zero in column F that has a value in the adjacent column E >=1720. Once that area in the table is found, I need the value from column A.

There are lots of numbers greater than 1720 in column E as well as negative numbers in column F.

I can post a sample data set tomorrow when I have access to my laptop. I greatly appreciate your help!

Andy

 

[Peter_SSs]

What I need is the smallest value greater than zero in column F that has a value in the adjacent column E >=1720. Once that area in the table is found, I need the value from column A.

OK, see if this is getting close. Note, however, that in my sample data there are two rows that contain the lowest value greater than zero in column F that have values >=1720 in column E. This formula returns the value from the first row that meets these conditions.

afinn211.xlsm
	A				E	F	G	H
6	a				23	3		g
7	b				265	4
8	c				1800	5
9	d				3000	8
10	e				2000	-6
11	f				4444	0
12	g				1750	4
13	h				100	1
14	i				1730	4
15	j				2	-2
16
Sheet1

Cell Formulas
Range	Formula
H6	H6	=INDEX(SORT(FILTER(A6:F24806,(F6:F24806>0)*(E6:E24806>=1720)),6),1,1)

 

[afinn211]

Thanks so much! That did it. I am not familiar with Index(Sort(Filter. I will definitely need to learn this one!

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.