Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
Training
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


Privacy Policy



FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

 

 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

VLookupClassic ( LookupValue ; SearchTableAndFieldName ; ReturnFieldName ; RangeLookup )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.8  (15 votes)
  Discuss this Custom Function

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

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).

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"


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed

Description:

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?)

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

This is my Custom Function and I want to edit it

Discuss:

There are no comments yet. Be the first to post a comment about this Custom Function! Please try to keep it brief & to the point. Anyone can post:

Your Name:
City/Location:
Comment:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 7 + 6 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. decFromHex ( hexValue )
  (Mon, Oct 16, 12:34pm)
2. MonthYearList ( startmonth ; startyear ; numbermonth ; short )
  (Mon, Oct 16, 3:38am)
3. ErrorDescription ( errorNumber )
  (Wed, Sep 27, 2:51am)
4. decodeEntities (text)
  (Wed, Sep 27, 1:22am)
5. HexidecimalToNumber ( HexidecimalValue ; counter )
  (Mon, Sep 25, 12:39pm)
6. FixedFieldConverter ( Fieldname ; f1 ; f2 ; f3 ; f4 ; f5 ; f6 ; f7 ; f8 ; f9 ; f10 ; f11 ; f12 ; f13 ; f14 ; f15 ; f16 ; f17 ; f18 ; f1
  (Fri, Sep 15, 12:34pm)
7. ShannonEntropy ( text )
  (Thu, Sep 07, 5:59am)
8. LetterCount ( text ; summary )
  (Thu, Sep 07, 5:53am)

RSS Feed of Custom Functions