Find date closest but before in offset columns

[kocemba]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Arrival date
[/TD]
[TD]Date
[/TD]
[TD]Text
[/TD]
[TD]Date
[/TD]
[TD]Text
[/TD]
[TD]Date
[/TD]
[TD]Text
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]01/01/2000
[/TD]
[TD]01/01/1991
[/TD]
[TD]xx
[/TD]
[TD]01/01/1999
[/TD]
[TD]yy
[/TD]
[TD]01/01/2015
[/TD]
[TD]zz
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0/01/2001
[/TD]
[TD]04/06/1998
[/TD]
[TD]cc
[/TD]
[TD]05/06/1999
[/TD]
[TD]vv
[/TD]
[TD]12/28/2000
[/TD]
[TD]bb
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Guys,

First timer here.

I need to find the date closest to but before. I have used index and match before but this time the dates are in every second column.

I used this formula in another case where I had the dates in every column: =INDEX(G2:DL2,MATCH(TRUE,G2:DL2>C2,0))

I have tried inserting an offset function in the index without any luck.

Can you guys help me out?
Thanks

 

[yky]

MATCH ignores text when searching for number. If the date is really date, not text, MATCH should do it.
Your problem here is that you need a sorted array if you want to use a match type other than 0.
In your case, you want to find "closest but before", which is "largest but less than or equal", you need to use match type 1 and that requires a sorted array.

 

[yky]

.

 

[yky]

Try this:

=LOOKUP(DATEVALUE,SMALL(A2:H2,{1,2,3,4,5,6,7,8}))

where DATEVALUE is the numeric value of a date, e.g. 33333 for April 5, 1991.