How to use vlookup to find the value based on time in another table (google sheet)?

Question

I'm trying to get the values for column C in Table 1 from column H by using vlookup. However, seems like the vlookup doesn't work and return a N/A. I had checked both ISDATE and ISNUMBER for data in column B and column G, they are the same.

One of the possible reason I can think of is because I'm using query function to get the data for column F and column G from Table 1. However, I'm not sure how to solve this so that I can use the vlookup function or any other method so that I can match the value based on the time.

I attached the google sheet link below in case you need it:

https://docs.google.com/spreadsheets/d/17coke3-oyDRLHgz79PDl3KX68kFOEte-aynVe-xEITU/edit?usp=sharing

Answer 1

I've added a new sheet ("Erik Help") with the following formula solution in C2:

=ArrayFormula({"Value";IF(B3:B="",,VLOOKUP(ROUND(B3:B,10),ROUND(G3:H,10),2,FALSE))})

This single formula returns all results for the column.

You were experiencing the effects of floating decimals, meaning numbers generated in different ways are actually different in memory to very fine degrees. I just used ROUND(...,10) on everything to limit that, and it works fine.