Find the first date less than cell and return adjacent value..

[JamesBowdidge]

I have 2 columns A and B

In A there are 20 dates that get larger as you go down starting on 20/12/2018 but not necessarily by 1 IE 20/12/2018 then 01/01/19 then 02/01/19 then 10/01/19 then 15/01/19 etc)

In column B there are 20 numbers starting from 1 and ascending but again not increasing by 1.. doesn't matter what they are.. they just increase..

I have a separate tab with sequential dates starting at 01/01/2019 for the whole year..

What I want to do is...

1 - on the separate tab with the year of dates, lookup/find the first date from A that is LESS than the date on the tab, then
2 - return the number in the adjacent cell B

I think I am having a brain fart cos I just cant work out how to do it...


so the first tab looks like this...



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]20/12/2018
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]1972[/TD]
[/TR]
[TR]
[TD]04/01/2019[/TD]
[TD]2982[/TD]
[/TR]
[TR]
[TD]09/01/2019[/TD]
[TD]6924[/TD]
[/TR]
[TR]
[TD]16/01/2019[/TD]
[TD]10880[/TD]
[/TR]
</tbody>[/TABLE]



Second tab looks like this, with the result im expecting in the second row.. I need to formula for this second row...


[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]01/01/2019[/TD]
[TD]02/01/2019[/TD]
[TD]03/01/2019[/TD]
[TD]04/01/2019[/TD]
[TD]05/01/2019[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1972[/TD]
[TD]1972[/TD]
[TD]1972[/TD]
[TD]2982[/TD]
[/TR]
</tbody>[/TABLE]

Been doing my head in all day..

thanks

James

 

[Fluff]

How about
=INDEX(Sheet1!$B$2:$B$6,MATCH(A1-1,Sheet1!$A$2:$A$6,1))

 

[JamesBowdidge]

-1... Jesus, thats all it was... I gave up with the index/match when all I needed was a goddam -1...

Thank you sooooo much..

CHeers

J

 

[Fluff]

You're welcome & thanks for the feedback