Match Function

[ASPENCO]

I wish to write a formula that will identify the first match in tw
different ranges with two different criteria. For example, if I hav
the following two ranges:


1, 5, 12, 15

1000, 2500, 3000, 5000

My objective is to write a formula that uses the criterion >10 for th
first range and >4000 for the second range and returns the number "4
where 15 and 5000 both meet the criteria. Have been trying to use
combination of the MATCH and AND formulas but the result is an "N/A
and there is a match in the data. Thanks for all the help as always

 

[BrianB]

This formula works to return the match or next highest
to 10 :-
=INDEX($A$1:$A$4,MATCH(10,$A$1:$A$4,-1))
to 4000 :-
=INDEX(B1:B4,MATCH(4000,B1:B4,-1))

For this to work the column must be sorted in *descending numerica
order*

 

[Jerry W. Lewis]

Assuming that your first condition row is in C1:F1 and your second
condition row is in C2:F2, then you could use an array formula like
=MIN(IF((C1:F1>10)*(C2:F2>4000),COLUMN(C1:F1),""))-COLUMN(C1)+1

The MIN() function returns the actual column number of the match, so the
trailing -COLUMN(C1)+1 rescales it to return a number between 1 and 4,
like the MATCH() function.

Don't forget to array enter (Ctrl-Shift-Enter)

Jerry

 

[Lorne]

If the numbers are in ascending order, try

MAX(MATCH(10,range1),MACTH(4000,range2))+1