Lookup problem.

Solved/Closed
pothole
Posts
5
Registration date
Monday January 19, 2015
Status
Member
Last seen
January 16, 2016
- Jan 19, 2015 at 06:12 AM
Babu546
Posts
7
Registration date
Tuesday January 20, 2015
Status
Member
Last seen
January 26, 2015
- Jan 21, 2015 at 07:25 AM
I have a column of 20 dates in col. B spaced by 2 or 3 weeks, and a number beside each one in col. C.
In cell C24 I need a formula that returns the value in col. C beside the last date and remains so until the current date reaches the next one in col. B, and then changes to that value.
I have been using Vlookup, Today, with If, and Or, but just can't seem to get it right.
Any help would be much appreciated.

4 replies

Do you mean that you want the value from Column C to be placed into C24 when the date in Column B matches the current date (or the date closest too but not higher than the current date)?
0
pothole
Posts
5
Registration date
Monday January 19, 2015
Status
Member
Last seen
January 16, 2016

Jan 20, 2015 at 04:39 AM
Yes that is correct, I want the value in C24 overwritten each time the date advances to the current date.
0
This returns the value next to the date that is the closest to but not greater than the current date.
I have assumed 'current date' is always equals TODAY.

=INDEX($C2:$C21,MATCH(TODAY(),$B2:$B21,1))

Put this in C24

Change the ranges according to your requirement.
0
Babu546
Posts
7
Registration date
Tuesday January 20, 2015
Status
Member
Last seen
January 26, 2015

Jan 21, 2015 at 07:25 AM
Hello Mr.RayH,

I Have a similar problem like this, i used your above code as per my requirement
which is

=INDEX(Master!$B$3:Master!$B$1067,MATCH(TODAY()+30,Master!$H$3:Master!$H$1067,0))

HERE I HAVE COLUMN " B " REPRESENTS " SOL ID " WHICH IS A UNIQUE IN MASTER SHEET,

HERE COLUMN RANGE IS BETWEEN A3 TO H 1067

COLUMN " H " REPRESENTS THE DUE DATE FOR THE SOLE ID

NOTE: SAME DUE DATE IS THERE FOR MORE THAN ONE SOLE ID

NOW MY MOTIVE IS TO PRINT THE SOLE ID WHOSE DUE DATE IS AHEAD OF ONE MONTH IN ANOTHER SHEET

FOR EXAMPLE :-
I HAVE 3 SOLE IDS 522,645 & 1601 DUE ON AFTER 30 DAYS i.e =TODAY()+30

NOW I WANT TO PRINT THESE IN TO ANOTHE SHEET SO THAT I CAN USE THESE UNIQUE SOL IDS IN VLOOKUP AND GET THOSE ROWS IN NEW SHEET

ANY ASSISTANCE FOR THIS ISSUE IS GREATLY APPRECIATED....
THANKS IN ADVANCE
0
pothole
Posts
5
Registration date
Monday January 19, 2015
Status
Member
Last seen
January 16, 2016

Jan 20, 2015 at 11:49 AM
Excellent! Seems to work OK. Thanks very much Ray.
0