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
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
=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.
- And thanks. Can't forget the gratitude.
- you are very welcome. so glad it helps