Lookup gives me wrong value

[stenberg98]

Hello

I'm trying to make a lookup function based on dates. i use this: =LOPSLAG($A4;'Master Geo'!A:AC;B$3)

The issue is that it gives the value from the wrong date - so if I lookup 20/12/1920 it gives me 19/12/1920 instead - why is that?

if I insert 0 or False it just says #I/T

Best regards

[BadlySpelledBuoy]

Hi and welcome.

I suspect the data has dates and times rather than just dates. Could we see some sample data in a workbook?

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

BSB

[stenberg98]

Sure :-)

I couldn't upload to the site, so heres a link for a MEGA link:

h t t p s : / / m e g a . n z / # ! w U c U A A K I

(Just remove the spaces :-) )

[BadlySpelledBuoy]

The link asks for a decryption key and it's not good forum etiquette to make those offering you assistance go hunting around for files.
If you want help with it, attach a slimmed down version here on the forum.

BSB

[stenberg98]

Sorry about that - triede to cut it down:

[BadlySpelledBuoy]

As suspected, all of your dates on the first sheet have a time element that is not displayed.

You could either strip that out or amend your formula to account for it. The below would be the formula in cell B4:

Please Login or Register  to view this content.

The +0.5 simply adds 12 hrs to the date in A4 so the formula can find an exact match in the first sheet.

BSB