Brian Dunning's FileMaker Custom Functions

VLookupClassic ( LookupValue ; SearchTableAndFieldName ; ReturnFieldName ; RangeLookup )

Works EXACTLY like Excel's VLOOKUP function: Searches in a specific field for the first (close or exact) match, and returns the value from a different field in the same record. -- This function can find nearby matches or exact matches, is NOT case sensitive, returns only one value, and will return #N/A if no match is found. Can pull data from any table (even from non-related tables).

  Average rating: 4.9 (15 votes) Log in to vote

Doug Staubach   Doug Staubach
FileMaker Poweruser
https://www.linkedin.com/in/dougstaubach

Share on Facebook Share on Twitter

  Sample input:
Assume that the current layout table is TableInventory, and we have an unrelated table called TableCatalog

Example1: VLookupClassic ( "101" ; TableInventory::ItemNumber ; TableInventory::Description ; "FALSE" )
... looks up item number 101, matches against the inventory, and returns Description "Lamp"

Example2: VLookupClassic ( "LAMP" ; "TableCatalog::Description" ; "ItemID" ; "FALSE" )
... looks up description, in an UNRELATED table (non-case-sensitive, so "LAMP"="Lamp"), and returns ItemID "101"

Example3: VLookupClassic ( "103" ; TableInventory::ItemNumber ; Description ; "TRUE" )
... looks up item number 103, but doesn't find it. Since RangeLookup is TRUE and closest match is 101, it returns "Lamp"

Example4: VLookupClassic ( "103" ; TableInventory::ItemNumber ; Description ; "FALSE" )
... looks up item number 103, but doesn't find it. RangeMatch is FALSE, so it returns "#N/A"

Example5: VLookupClassic ( ThisItem ; TableInventory::ItemNumber ; Description ; "FALSE" )
... looks up contents of ThisItem field, matches it against the inventory, and returns Description "Chair"

Example6: VLookupClassic ( "" ; TableInventory::ItemNumber ; Description ; "FALSE" )
... because RangeLookup is FALSE, this looks for the first empty ItemNumber, and returns Description "Water"
  Sample output:
Example1: "Lamp"

Example2: "101"

Example3: "Lamp"

Example4: "#N/A"

Example5: "Chair"

Example6: "Water"

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Works EXACTLY like Excel's VLOOKUP function: Searches for the first (close or exact) matching value in a specific field, and returns a value from a different field in the same record. -- This function can find nearby matches or exact matches, is NOT case sensitive, returns only one value, and will return #N/A if no match is found. Can pull data from any table (even from non-related tables).

If you are looking for different capabilities (similar, but supports multiple lookup values and multiple return values, is case-sensitive, and doesn't return #N/A), check out my other function 'VLookup'

NOTES:

Like most other ExecuteSQL-based queries, this function doesn't need (and it doesn't care) if a relationship exists between the current layout and the table that you are searching. If a relationship does exist, this function will look at ALL of the data in the target table, not just the "matching" relational data or filtered data that would normally appear within a data portal. In essence, this is a great tool for looking up data in a calculated field, but it will ignore any sorting and filtering that you might have enabled through native FileMaker functions, so be aware of that.

If a relationship does NOT exist between the active layout table and the vlookup table (parameters 1 and 3), then the tablename and fieldnames must be enclosed in quotes like this ... "table::field" or "field"

Although it relies heavily on FileMaker's built-in ExecuteSQL capabilities, this function does not require knowledge of SQL syntax, and it bypasses the strict naming rules that ExecuteSQL normally requires (all field and table names are automatically "escaped" inside this function, so they won't cause any errors, and search values are automatically converted as necessary to avoid "field type mismatch" errors).

Tested with FileMaker Pro v14 and v13 (this might also work with FileMaker Pro v12 -- main requirement is the ExecuteSQL function)

Since this version of the VLookup function does not allow the user to search for multiple matches (no lists), it does not have any recursion, and the returned value does not end with a trailing "¶" character.

INPUTS:
(1) The first parameter (LookupValue) can be static text enclosed in quotes like "Apple" or the name of a field that contains a lookup value
- If you want to provide a fieldname for the first parameter, and the field is NOT from the current layout table or a related table, the it must be enlosed in quotes like this "table::field"
- If you want to provide a fieldname for the first parameter, and the field is from the current layout table or a related table, it does not have to be enclosed in quotes
(2) The second parameter (SearchTableAndFieldName) must include both a tablename and a fieldname, formatted like this ... Table::Field.
- A single fieldname from the current layout table or a related table does not have to be enclosed in quotes
- If the fieldname is from an urelated table, you must enclose the value in quotes like this ... "table1::field1"
(3) The third parameter (ReturnFieldName) can be a single fieldname like "Price", or it can be a tablename::fieldname (must be the same as the tablename in second parameter).
- If the fieldname is from an urelated table (or if you are specifying multiple field names), you must enclose the value in quotes like this ... "table1::field2"
- A single fieldname from the current layout table or a related table does not have to be enclosed in quotes
(4) The last parameter (RangeLookup) can be text "True" or "False" - or numeric (zero is False, and any other number is True), If the last parameter is empty or is any other text string, the resulting value will be "False"

OUTPUTS:
If RangeLookup is set to "FALSE", then we are looking for exact matches only. If no exact match is found, then this function will return "#N/A" (like Excel does)
If RangeLookup is set to "FALSE" and the LookupValue is empty "", then the function will look for empty values in the SearchField.
If RangeLookup is set to "TRUE", and there is at least one value that is "less than or equal to" the lookup value, then this function will return a the nearest close match.
If RangeLookup is set to "TRUE", and there is no value that is "less than or equal to" the lookup value, then this function will return "#NA".
If RangeLookup is set to "TRUE", and the LookupValue is empty "", then the function will return "#N/A".

EXAMPLES:
Assume that the current layout table is TableInventory, and we have an unrelated table called TableCatalog
Example1: VLookupClassic ( "101" ; TableInventory::ItemNumber ; TableInventory::Description ; "FALSE" ) ... looks up item number 101, matches against the inventory, and returns Description ... "Lamp"
Example2: VLookupClassic ( "LAMP" ; "TableCatalog::Description" ; "ItemID" ; "FALSE" ) ... looks up description "lamp" (not case sensitive) in an UNRELATED table, and returns the ItemID ... "101"
Example3: VLookupClassic ( "103" ; TableInventory::ItemNumber ; Description ; "TRUE" ) ... looks up item number 103, but doesn't find it. The nearest lower value is 101, so it returns ... "Lamp"
Example4: VLookupClassic ( "103" ; TableInventory::ItemNumber ; Description ; "FALSE" ) ... looks up item number 103, but doesn't find it. RangeMatch is FALSE, so it returns ... "#N/A"
Example5: VLookupClassic ( ThisItem ; TableInventory::ItemNumber ; Description ; "FALSE" ) ... looks up the contents of ThisItem field, matches it against the inventory, and returns Description "Chair"
Example6: VLookupClassic ( "" ; TableInventory::ItemNumber ; Description ; "FALSE" ) ... because RangeLookup is FALSE, this looks for the first empty ItemNumber, and returns Description "Water"

New in v1.1:
(1) Allows user to query on empty string values "", but only if RangeLookup is set to FALSE. -- Added this capability to match Excel.

(Let me know if you like it?)

 

Comments

Dilio   Dilio, Honduras
May 28, 2018
good afternoon the function presents probles in the lin ;RESULT1 = ExecuteSQL ( MyCmd1 ; "" ;""' ) of how I could solve it or I am failing in some step
 
Doug Staubach   Doug Staubach, Denver, CO, USA
Jun 11, 2018
Hi Dilio:

It looks like there is an extra ' character in the line that you posted:

;RESULT1 = ExecuteSQL ( MyCmd1 ; "" ;""' ) // incorrect
;RESULT1 = ExecuteSQL ( MyCmd1 ; "" ;"" ) // correct

Please try making this change and let me know if you're still having an error.

Thanks,
Doug
 
Dilio   Dilio, Honduras
Aug 6, 2018
Hi, I still have the same problem, this function can not be found and it puts me in shadow in ExecuteSQL
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.