VLOOKUP

[Ziphius]

I know there is a custom function that is equivalent to Excel's VLOOKUP, or at least to find exact matches. The Excel function allows for it's third parameter to be FALSE (find exact match) or TRUE (find closest match). I have a table of Timestamped records that need location positions. I have a second table of GPS track location positions that are Timestamped every 10 seconds. I need the function to be able to find the closest matching Timestamp and grab the location from the GPS table. Is there a solution for this? I'm using Filemaker Pro 13 Advanced. Thanks.

[comment]

Why cannot this be achieved using  Filemaker's native lookup feature?

[Ziphius]

My limited understanding of the LOOKUP feature was that the tables had to be related, is that not correct?

[bruceR]

That is correct; but why do you see that as a problem, and how else would you expect it to be accomplished?

[Ziphius]

I guess since there was a custom function for Excel's version of VLOOKUP using the "FALSE" parameter (exact match), I was hoping there would be one that does the "TRUE" parameter (closest match) as well.  This just makes transfering an Excel file that uses VLOOKUP into Filemaker simple. Since there isn't, I've attached my best attempt at achieving what I need to achieve using the LOOKUPNEXT and LOOKUP functions. If there is an actual custom function or (I'm sure) a better way, please let me know. At least this seems to be working. Thanks.

LookupTest.zip

Edited June 4, 2015 by Ziphius

[Wim Decorte]

You should get out of the Excel way of thinking, it is not going to get you anywhere. Trying to mimic what you have in Excel is only going to prolong the learning curve.