2 Replies Latest reply on Feb 13, 2015 8:34 AM by Doug Staubach

Looking for suggestion to how to replace Excel Vlookup function.

gregdc Sep 22, 2014 6:03 AM

Title

Post

I am converting a set of excel spreadsheets to FM13. Several of the sheets contain column with the VLookUp function. (ie Vlookup(ID, ParentNameTable, 3, False) == lookup in the partent table, using ID, return the value in the Partent tables third column if there is an exact match with ID)

I am looking for suggestions for how to covert this function to FM13 to take the ID in the record, look it up in another table and return a blank if there is no exact match, or the matching ID field's value. I have looked at Lookup function in FM13 but do not really understand it. I have thought,also, of using an ExecuteSQL but not sure that that is the best path.

Can someone familiar with this sort of conversion give me some suggestions/examples? Thanks GregDC

1. Re: Looking for suggestion to how to replace Excel Vlookup function.

philmodjunk Sep 22, 2014 6:08 AM (in response to gregdc)

Not only can you do this in FileMaker, there's more than one way to produce that result. But in each case, this starts by defining a table in your file as the table of values to be looked up where you have one field with the ID and another with the value to be looked up. You then define a relationship linking that table to the table where you want to see these values being looked up.

Then, in Field Options, there's an auto-enter tab. You can set either an auto-enter calculation or a Looked up value option to do what you describe.

Caulkins Consulting, Home of Adventures In FileMaking
Like Show 0 Likes(0)

Actions
2. Re: Looking for suggestion to how to replace Excel Vlookup function.

Doug Staubach Feb 13, 2015 8:34 AM (in response to gregdc)

Hi Greg:

I created a custom function called VLookup that might meet your needs (not a sales pitch - it's free).

You'll need to have FileMaker Pro Advanced (to create your own functions), and it would only work with version 12 or higher (because it uses ExecuteSQL and recursive loops).

Just copy the source code from the following website, and paste it into a new function (called VLookup), using the menu command sequence File - Manage - Custom Functions.

https://www.briandunning.com/cf/1740

Best wishes,
Doug Staubach
Like Show 0 Likes(0)

Actions

Go to original post