SOLVED

VLOOKUP with random input data

%3CLINGO-SUB%20id%3D%22lingo-sub-3218962%22%20slang%3D%22en-US%22%3EVLOOKUP%20with%20random%20input%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3218962%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20issue%20with%20infrequent%20data%20being%20fed%20from%20a%20sensor%20at%205%20minute%20intervals%20into%20a%20pivot%20table%20(See%20fig.1).%20Many%20times%20the%20data%20does%20not%20populate%20for%20some%20reason%20or%20the%20other%20and%20it%20provides%20a%20blank%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20a%20VLOOKUP%20to%20reflect%20the%20data%20on%20another%20table%20that%20has%20specific%20times%20for%20input.%20Since%20the%20pivot%20table%20doesn't%20always%20have%20data%20at%20that%20specific%20time%2C%20it%20returns%20no%20value.%20(See%20fig.2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20do%20is%20have%20the%20VLOOKUP%20grab%20the%20closest%20data%20to%20the%20specific%20time%20and%20reflect%20that%20value%20in%20the%20table.%20My%20current%20VLOOKUP%20formula%20is%26nbsp%3B%3CEM%3E%3DIFERROR(VLOOKUP(B11%2CSys.PvSCD!A%3AB%2C2%2CTRUE)%2C%22-%22)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3218962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3219113%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20random%20input%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219113%22%20slang%3D%22en-US%22%3ESince%20you%20have%20a%20date%2Ftime%20column%20that%20is%20ascending%20I%20would%20recommend%20using%20something%20like%3A%3CBR%20%2F%3E%3DXMATCH(%5Bdate-time-to-find%5D%2C%20FILTER(%5Bdate-times%5D%20%2C%20%5BData%20Value%5D%26gt%3B0)%2C%20Z%2C%202)%3CBR%20%2F%3Eand%20that%20Z%20can%20be%20-1%20for%20the%20value%20before%20or%201%20for%20the%20value%20after%20or%20you%20can%20find%20both%20and%20either%20average%20or%20use%20interpolate%20a%20value%20in%20between%20(you%20could%20use%20TREND%20for%20that).%3CBR%20%2F%3EYou%20might%20be%20good%20to%20use%20the%20LET()%20formula%20to%20minimize%20re-calculation%20of%20the%20match%20and%20such.%3CBR%20%2F%3ESo%20my%20question%20is%20just%20using%20the%20previous%20or%20subsequent%20value%20good%20enough%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3219148%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20random%20input%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20a%20lot%20of%20that%20went%20over%20my%20head.%20I'm%20kind%20of%20learning%20as%20I%20go.%20But%20yes%20any%20value%20before%20or%20after%20the%20specific%20time%20would%20be%20fine.%20I%20just%20need%20closest%20to.%3C%2FP%3E%3CP%3EYou're%20saying%20not%20to%20use%20VLookup%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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),"-")

 

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?

@mtarler 

 

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?

best response confirmed by sconway1985 (Occasional Contributor)
Solution
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.
And thanks. Can't forget the gratitude.
you are very welcome. so glad it helps