Forum Discussion
VLOOKUP with random input data
Hello.
I have an issue with infrequent data being fed from a sensor at 5 minute intervals into a pivot table (See fig.1). Many times the data does not populate for some reason or the other and it provides a blank value.
I am using a VLOOKUP to reflect the data on another table that has specific times for input. Since the pivot table doesn't always have data at that specific time, it returns no value. (See fig.2)
What I would like to do is have the VLOOKUP grab the closest data to the specific time and reflect that value in the table. My current VLOOKUP formula is =IFERROR(VLOOKUP(B11,Sys.PvSCD!A:B,2,TRUE),"-")
- Well.... Here is a more 'simple' version that still uses VLOOKUP:
=VLOOKUP(MAXIFS(PvSCD!A:A,PvSCD!A:A,"<="&B11,PvSCD!B:B,">0"),PvSCD!A:B,2)
so what this does is it finds the 'lookup' value for the VLOOKUP based on the MAX value that is <= the value you are looking for AND the Data value is >0
6 Replies
- Since you have a date/time column that is ascending I would recommend using something like:
=XMATCH([date-time-to-find], FILTER([date-times] , [Data Value]>0), Z, 2)
and that Z can be -1 for the value before or 1 for the value after or you can find both and either average or use interpolate a value in between (you could use TREND for that).
You might be good to use the LET() formula to minimize re-calculation of the match and such.
So my question is just using the previous or subsequent value good enough?Sorry a lot of that went over my head. I'm kind of learning as I go. But yes any value before or after the specific time would be fine. I just need closest to.
You're saying not to use VLookup?
- Well.... Here is a more 'simple' version that still uses VLOOKUP:
=VLOOKUP(MAXIFS(PvSCD!A:A,PvSCD!A:A,"<="&B11,PvSCD!B:B,">0"),PvSCD!A:B,2)
so what this does is it finds the 'lookup' value for the VLOOKUP based on the MAX value that is <= the value you are looking for AND the Data value is >0
