Forum Discussion
Solved
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 i...
- 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
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?
=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- Bud, that was so simple and so effective. I have now incorporated this formula into all sensor VLOOKUPS just in case I run into these issues on the other ones. Well done sir, well done.
