How to use two INDEX and MATCH statements to return specific value?

Question

I have a situation where I wish to find the closest value in an array and then use that to lookup another value.

Here is my example.

In cell H4, users can enter a desired value (in minutes [mins]) that they wish to run a specific test. In this instance, Test 1 is selected (cell I4). I wish to obtain the closest value in B4:B9 (mins column of array) that is equal to or less than the value in cell H4. My formula so far returns the correct number (5; cell J4), but I'd like to extend my formula to return the value for var1 (column C) that is associated with the value 5.

My result should be 113, as 113 is associated with 5 mins for Test 1.

How can I continue my formula in cell J4 (see below) to return the desired number for var1 based on the closest value for mins?

=IF(I4="","",INDEX(A4:C9,MATCH(H4,IF(A4:A9=I4,B4:B9),1),2))

Answer 1

This could be used also: =LOOKUP(H4,FILTER(B4:C9,A4:A9=I4))

Answer 2

The simplest workaround of this is to use OFFSET function like this

=IF(I4="","",OFFSET(INDEX(A4:C9,MATCH(H4,IF(A4:A9=I4,B4:B9),1),2),0,1))

This always return the cell to the right of your function result.

Answer 3

Based on your values for mins, this might work (corrected handling when there's no match):

=INDEX(B4:C9, MATCH(H4, B4:B9 * IF(A4:A9 = I4, 1, NA())), )

If you're using an older version, please select J4:K4 and enter as an array formula with Control + Shift + Enter.

Answer 4

Apply a small modification to your formula to return the whole row then drop the first cell:

(#1) =DROP(IF(I4="","",INDEX(A4:C9,MATCH(H4,IF(A4:A9=I4,B4:B9),1),)),,1)

Or you can use this formula:

(#2) J4=LET(arr,FILTER(B4:C9,A4:A9=I4),XLOOKUP(H4,INDEX(arr,,1),arr,,-1))

For Excel less 365 you can use two formulas or the array formula:

(#3) =INDIRECT(MATCH(H4;IF(A4:A9=I4;B4:B9);1)&":"&MATCH(H4;IF(A4:A9=I4;B4:B9);1)) B:C

To enter the array formula, select J4:K4, enter the formula and press Ctrl+Shift+Enter.

Answer 5

The simplest version which will work with all versions of Excel:

=SUMPRODUCT((A2:A7=F3)*(B2:B7=G3)*C2:C7)

You can also use:

=SUMIFS(C2:C7, A2:A7, F3, B2:B7, G3)

Or:

=FILTER(C2:C7,(A2:A7=F3)*(G3=B2:B7))

Or if you REALLY want to do it with INDEX/MATCH:

=INDEX(C2:C7,MATCH(1,(A2:A7=F3)*(B2:B7=G3)))