In attachment Raw Data sheet is lookup table.
In Sheet 1 all cells are formulas except A2.
All lookups in column B work until row 22 where I had to replace the formula for A22 [from A21] with direct data [copied from the table].
If you copy the date formula down from the cell above - A21 - the lookup fails as do all those below it. I have even shown the numerical format for this data on the right which doesn't change using the formula.
how can you say it failed?
i look at it manually and they are correct.
24/01/2015 11,860.3
31/01/2015 10,842.6
7/02/2015 11,444.5
time is all 4:00:00 PM
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hi Ralph and welcome to the forum,
Try the Approx form of VLookup with the True instead of Exact or False. Change formula to this
=VLOOKUP(A21,'Raw Data'!$A$4:$B$7491,2,TRUE)
My guess is rounding error or something. I get what you are asking but don't have a better answer than make it True.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Yes, thank you MarvinP. Using TRUE works. I suspected it was some sort of obscure accumulated rounding error even though the formula's numeric value was identical to that for the direct data.
However, what this means is that all dates from row 22 onwards are only approximations to those in the lookup table and presumably the differences a likely to increase. At what point does this difference become too great so that another value in the table is a better approximation?
Hey Ralph,
Remember you need to sort your data from small to big if your use the "TRUE" in the VLookup. Maybe you should use an MRound() formula on your date and times to get them to the closest minute. Maybe a False would work then with the VLookup??
Thanks again Marvin,
Thanks for the reminder on sorting with TRUE but the table is progressively populated so there's no problem there. As for the possibility of "approximation drift" over time, the data is logged ever 30 minutes and the numerical difference is 0.0208333334012423 which I don't think will worry me [i.e. in my lifetime]. Thank you again for your solution.
Here's an alternative way of selecting the time slot (16:00 every day) that is chosen. I used offset to calter the date and Index Match to return an exact match
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I spoke too soon. I find that TRUE is giving me the data for 3:30 PM so it must be selecting the largest value in the table which is less than the reference value [not the closest value to the reference value]. I'll try my luck with MRound() !
It is finally working using ROUND() with TRUE. Doesn't work otherwise.
Did you take a look at ghe OFFSET solution?
Hi Vladimir.
Sorry, but if it was that easy I wouldn't have needed the forum. Clearly operating with date values to the exactitude required by VLOOKUP is rather a dark art. I'll be putting this experience under my belt for future reference. The forum has indeed been helpful.
Hi Glenn
Yes, I did but the lookup table is imported from elsewhere so I don't want to lean too heavily on is structure or completeness. I need to be able to define the data I need without making such assumptions.
OK. Fair enough. I had assumed that if the data was logged every 30 minutes, that there would always be 48 rows between every 1600 reppt time. Hence my choice of OFFSET.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks