vlookup only returns last row or NA (I tried suggestions from other threads)

[Johnny00]

I am trying to lookup a date/time from sheet 1 and return the corresponding high value from sheet 2. I have tried rounding the date/time, didn't work. Tried changing formats for columns, didn't work. No idea what I'm missing, but I'm a novice so it's likely that I'm missing everything important. Here's what I have been trying:

=VLOOKUP(B2,Sheet2!B2:E285120,3,FALSE)
and
=VLOOKUP(ROUND(B2,5),Sheet2!B2:E285120,3,FALSE)
both return #N/A

If I remove the range_lookup it returns the appropriate column but only from the last row in the array
ex. =VLOOKUP(B2,Sheet2!B2:E285120,3)

I have been trying to figure this out for many hours, please help

 

[Toadstool]

Hi Johnny00,

Yes, it looks like a rounding challenge so you could insert a new column C on Sheet2 and make it =ROUND(B2,5) and copy down. Then on Sheet1 try the =VLOOKUP(ROUND(B2,5),Sheet2!B2:E285120,3,FALSE)

Another method would be to insert column C on Sheet2 and change the date and time to text with =TEXT(B2,"mm/dd/yyyy hh:mm")
Then on Sheet1 make the lookup =VLOOKUP(TEXT(B2,"mm/dd/yyyy hh:mm"),Sheet2!B2:E285120,3,FALSE)

 

[Alex Blakenburg]

I agree with Toadstool that a helper column on Sheet 2 with rounding would be useful.

You can try this:-
which looks for a date/time that falls within a range of a few seconds.

=LOOKUP(2,1/
             ((Sheet2!$B$2:$E$285120>=ROUNDDOWN($B2,5))*
               (Sheet2!$B$2:$E$285120<=ROUNDUP($B2,5))),
     Sheet2!D$2:D$285120)

 

[Johnny00]

Thank you Toadstool, the text option worked. I still couldn't get the rounding method to work, but text works for me.

 

[Toadstool]

Thank you Toadstool, the text option worked. I still couldn't get the rounding method to work, but text works for me.

You're welcome!