Matching TODAY() using VLOOKUP

[Denny57]

I am maintaing a Worksheet containing multiple columns of information which will be used in a Dashboard.

I am also displaying the current date information on a separate Worksheet. Ideally I would like to use the VLOOKUP function to pull details from the database.
The Information is uploaded into the database Worksheet via a user form in which the date is determined using DTPIcker.

LOOKUP FUNCTION
=VLOOKUP(E2,"Sheet1"!A:A,11,False)

Cell E2 contains TODAY() and using the Formula Wizard shows a 5 digit date reference number
Sheet 1 Column A records show in the date format dd/mm/yyyy in the format bar
So I cannot get a match.
I have also tried changing the format of Cell E2 to dd/mm/yyyy and added the date manually but without any success

Is there a formula that I can apply either to the VLOOKUP function or to Cell E2 to find a match.

Many thanks

 

[KOKOSEK]

Are You sure that in col. A of Sheet1 (database records) you've got date not text which looks like date (TODAY() is a number).

 

[Peter_SSs]

LOOKUP FUNCTION
=VLOOKUP(E2,"Sheet1"!A:A,11,False)

That is not a valid formula

• Sheet1 should not have double quotes around it
• You cannot look up in the 11th column if you only give the formula one column to use (A:A)

If you manually find in Sheet1 today's date and say it is in A20, what to you get if you put this formula in a vacant cell in Sheet1?
=ISNUMBER(A20)

 

[Denny57]

Are You sure that in col. A of Sheet1 (database records) you've got date not text which looks like date (TODAY() is a number).

Thank you for clarifying the format of TODAY(). In column A this has the custom format dddd dd mmmm yyyy and the format in the formula bar is automatically showing as dd/mm/yyyy.

I have decided to create a form which I understand how to code which should allow me to search records by date.

The current problem I have is that in my Daily Summary Worksheet I have used code from a previous file which calls records from the database. Unfortunately, some details are added to daily records during the day. The code I am using does not specify "CurrentRow" and consequently calls records from the previous record when a cell in the current row is empty.

Sample Formula to select information from column H
=LOOKUP(2,1/('Daily Records'!H:H>0),'Daily Records'!H:H)
For the life of me I cannot remember why I used this code and how it is actually pulling the information, just that it calls the last populated record in Column H.

Ideally, I would like this to capture the current row / last row value of cell H even if it is null

I have tried by changing the formula to
=LOOKUP(2,1/('Daily Records'!H:H)
but although the cell value is null (empty) I receive a value of 0.2

 

[Denny57]

That is not a valid formula

• Sheet1 should not have double quotes around it
• You cannot look up in the 11th column if you only give the formula one column to use (A:A)

If you manually find in Sheet1 today's date and say it is in A20, what to you get if you put this formula in a vacant cell in Sheet1?
=ISNUMBER(A20)

Thank you for your response. I can confirm the format of the details in column A is a number.

When I change the formula to

=VLOOKUP(E3,'Daily Records'!A:H,11,FALSE) (Using the VLOOKUP wizard) i get the response #REF!

If TODAY() and the entries in Column A are both number format then I would expect a match. However, column 11 (K) contains a formula displayed as a number. Perhaps this is why I call see the value of the looked up cell.

 

[Peter_SSs]

When I change the formula to

=VLOOKUP(E3,'Daily Records'!A:H,11,FALSE) (Using the VLOOKUP wizard) i get the response #REF!

Yes, you would get #REF! because you are telling the formula to look in column 11 of the range A:H but that range only has 8 columns!

If you are looking for the result to come from column H then change the 11 to 8
If you are looking for the result to come from column K then leave the 11 and change the range to A:K

 

[Denny57]

Yes, you would get #REF! because you are telling the formula to look in column 11 of the range A:H but that range only has 8 columns!

If you are looking for the result to come from column H then change the 11 to 8
If you are looking for the result to come from column K then leave the 11 and change the range to A:K

Thank you.. Obvious error on my part. Lack of sleep!!!

 

[Peter_SSs]

Thank you.. Obvious error on my part. Lack of sleep!!!

You are welcome. Thanks for the follow-up.