Vlookup to look up value for Previous Day in Col F

[Howardc1001]

I have a dates in Col D and want to lookup the value in Col F for todays date - 1 i.e. previous date


I have tried to set this up , but get an error


I have attached sample data


It would be appreciated if someone could kindly assist me

[Gregb11]

1. First issue - the dates you have in the file are not just dates, but a date and time. Change them to just dates.
2. Formula in B1 can be either:

=INDEX(F2:F16,MATCH(TODAY()-1,D2:D16,0))
or
=VLOOKUP(TODAY()-1,$D$1:F21,3,FALSE)

[bebo021999]

If you want to keep same format for date-time, try:

Please Login or Register  to view this content.

Note: If yesterday was missing, the LOOKUP with returns the value of newest day from yesterday (nth day before yesterday)

[Howardc1001]

Thanks for the help Guys. Formula works perfectly

The Range in Col D & F changes each day

I tried to record the code, and then to amend it so that it pick up the last row -1, but cannot get it to work

Please Login or Register  to view this content.

It would be appreciated if you could assist me

[Gregb11]

You can extend the formula, like:

=INDEX(F2:F160000,MATCH(TODAY()-1,D2:D160000,0))

In fact, you could just do the whole column:

=INDEX(F:F,MATCH(TODAY()-1,D:D,0))

[Howardc1001]

Thanks Greg. I now get #N/A


I have attached my sample data

[AliGW]

Try an approximate match on TODAY():

=INDEX(F:F,MATCH(TODAY(),D:D,1))

[Howardc1001]

Thanks ALI


Formula works perfectly

[AliGW]

People often forget about the approximate match setting.