Find date closest but before in offset columns

[kocemba]

ID	Arrival date	Date	Text	Date	Text	Date	Text
1	01/01/2000	01/01/1991	xx	01/01/1999	yy	01/01/2015	zz
2	0/01/2001	04/06/1998	cc	05/06/1999	vv	12/28/2000	bb

<tbody>
</tbody>

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.