Matching with multiple criteria

[jkackley724]

I'm trying to write a formula that returns a specific date from an array based on two criteria.

Spreadsheet set up:
Row 1 A1:H1 (array of dates)
Row 2 A2:H2 (series of number, 0,4,8,12, etc.)
Column A3:A6 (series of numbers, 0,3,5,8,13,14, etc.)

I want to populate B4:B6 with dates, based on the numbers in A3:A6 and A2:H2. When the number in A4 is greater than the numbers in A2:H2 but also less than or equal to the numbers in A2:H2, I want that date returned. Example below, when 5 is between 4 & 8, I want the date when that occurs (January 31, 2018). Is this possible?


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]12/31/2017[/TD]
[TD]1/31/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/31/2018[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mart37]

=index($a$1:$h$1,match(sumproduct(large((($a$2:$h$2-a3>=0)*($a$2:$h$2)),countif($a$2:$h$2,">="&a3))),$a$2:$h$2,0))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(INDEX($A$1:$H$1,MATCH(1,IF(ISNUMBER($A$1:$H$1),IF($A$2:$H$2>=$A3,IF($A3<=$A$2:$H$2,1))),0)),"")