CODEWITHSUNDEEP
indexinggoogle-sheets
Chef1075
Chef1075

Reputation: 2724

GoogleSheets INDEX MATCH for closest value between date range

in Google Sheets, I'm trying to return the account name associated with the min absolute closest value between a date range using INDEX MATCH in Excel.

=ARRAYFORMULA(INDEX(B2:B,MATCH(MIN(ABS(C2:C-$G$5)),ABS(C2:C-$G$5),(A2:A>=G2)*(A2:A<=G3))))

Here are the data values:

Here is a sample of some dummy data:

enter image description here

Right now the formula is working ... sorta. It is returning an account name associated with the min absolute closest value but not from the date range specified by:

(A2:A>=G2)*(A2:A<=G3)

Here is is returning account "A" as it is the first value and closest to the guess, but it is OUTSIDE of the date rage specified by G2 and G3.

The correct answer which should be returned from this example is "C", from cell B4.

What am I doing wrong with this formula?

Thanks!

Upvotes: 3

Views: 5057

Answers (1)

Sangbok Lee
Sangbok Lee

Reputation: 2229

The 3rd parameter of match() should be -1, 0, or 1. So basically (A2:A>=G2)*(A2:A<=G3) part in your formula is nothing. If you want to match between some ranges, you can use filter(). So your formula should be like this.

=arrayformula(
   index(
     filter(B2:B, A2:A >= $G$2, A2:A <= $G$3), 
     match(
       min(abs(filter(C2:C, A2:A >= $G$2, A2:A <= $G$3) - $G$5)), 
       abs(filter(C2:C, A2:A >= $G$2, A2:A <= $G$3) - $G$5), 
       0
     )
   )
 )

I tested it and it worked. Here is a sample sheet.

Upvotes: 3

Related Questions