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 - Show more from this author
https://www.linkedin.com/in/dougstaubach/

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)

// Version 1.1 -- Created by Doug Staubach 2016-Mar-2, Revised 2016-Mar-4 -- http://www.briandunning.com/cf/1862
// 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'
Let ([
          VAR1 = If ( EvaluationError ( GetFieldName ( SearchTableAndFieldName ) ) ; SearchTableAndFieldName; GetFieldName ( SearchTableAndFieldName ) )    // bypass 102 errors (unrelated table)
         ;VAR2 = If ( PatternCount ( VAR1 ; "::" ) > 0 ; GetValue ( Substitute ( VAR1 ; "::" ; "¶" ) ; 1 ) )                                                                                               // get tablename from lookup field
         ;VAR3 = If ( EvaluationError ( GetFieldName ( ReturnFieldName ) ) ; ReturnFieldName; GetFieldName ( ReturnFieldName ) )                                               // bypass 102 errors (unrelated table)
         ;VAR3 = Substitute ( VAR3 ; VAR2 & "::" ; "" )                                                                                                                                                                     // strip tablename from return field(s)
         ;VAR4 = If ( LookupValue="" ; "" ; LookupValue )
         ;CloseMatch = 
               Case (
                    IsEmpty ( RangeLookup ) ; 0                                                                                 // If RangeLookup is an empty string (omitted), then CloseMatch is FALSE
                   ;Upper ( Trim ( RangeLookup ) ) = "TRUE" ; 1                                                         // if RangeLookup is set to the textvalue TRUE, then CloseMatch is TRUE
                   ;Int ( RangeLookup ) < 0  or Int ( RangeLookup ) > 0 ; 1                                        // if RangeLookup is set to a non-zero number, then CloseMatch is TRUE
                   ;0                                                                                                                          // otherwise, CloseMatch is set to FALSE (and false is the default)
               ) // end case
         // -------
         ;FLD1 = "\"" & GetValue ( Substitute ( VAR1 ; "::" ; "¶" ) ; 2 ) & "\""                                   // get the lookup field name and format it for SQL-safe processing
         ;TBL1 = "\"" & VAR2 & "\""                                                                                              // get the TABLE name and format it for SQL-safe processing
         ;FLD2 = "\"" & VAR3 & "\""                                                                                              // get the return field name and format it for SQL-safe processing
         ;TYP1 = MiddleWords ( FieldType ( Get ( FileName ) ; VAR1 ) ; 2 ; 1 )                               // get the field TYPE from the search field (Text, Number, Date, Time, Timestamp, or Container)
         // -------
        ;MyCmd1 =
            "SELECT COUNT (*) FROM " & TBL1 & " WHERE "                                                           // FIRST, we need to count and see if at least one record matches (if no match is found, we will return "#N/A")
            & Case (
                   VAR4 = "" and CloseMatch = 0                                                                            // if the LookupValue is empty "" and CloseMatch is FALSE, then exact match is "", so find empty (NULL) values
                        ;FLD1 & " IS NULL "
                  ;VAR4 = "" and CloseMatch = 1                                                                            // if the LookupValue is empty "", and CloseMatch is TRUE, then we will return "#N/A" (because "" has no close match)
                        ;"1=0"
                  ;TYP1 = "Text" // if the lookup field is a text field ...
                        ;"UPPER(" & FLD1 & ")"                                                                                      // to allow this function to be non-case-sensitive, we must uppercase all values in the lookup field
                        & If ( CloseMatch ; " <= " ; " = " )                                                                     // if CloseMatch is True, we will search for an approximate match (not an exact match) 
                        & "UPPER('" & GetAsText ( VAR4 ) & "')"                                                             // matching text must be enclosed in single quotes for SQL
                  ;TYP1 = "Date" // if the lookup field is a date field ...
                        ;FLD1
                        & If ( CloseMatch ; " <= " ; " = " )                                                                     // if CloseMatch is True, we will search for an approximate match (not an exact match) 
                        & "DATE " & "'"                                                                                                 // format as ISO date for SQL comparison
                        & Year ( GetAsDate ( VAR4 ) ) & "-"
                        & Right ( "0" & Month ( GetAsDate ( VAR4 ) ) ; 2 ) & "-"
                        & Right ( "0" & Day ( GetAsDate ( VAR4 ) ) ; 2 ) & "'"
                  // else - if the lookup field is anything else ...                                                      // Note: this could be expanded to handle other types of fields (Time, Timestamp or Container)
                        ;FLD1
                        & If ( CloseMatch ; " <= " ; " = " )                                                                    // if CloseMatch is True, we will search for an approximate match (not an exact match) 
                        & GetAsNumber ( VAR4 )                                                                                 // matching number must never be enclosed in single quotes for SQL (causes type mismatch)
               ) // end case
        ;RESULT1 = ExecuteSQL ( MyCmd1 ; "" ; "" )
        ;MyCmd2 =
            "SELECT " & FLD2 & " FROM " & TBL1 & " WHERE "                                                        // SECOND, we will try to get the actual information that the user asked for
            & Case (
                   VAR4 = "" and CloseMatch = 0                                                                            // if the LookupValue is empty "" and CloseMatch is FALSE, then exact match is "", so find empty (NULL) values
                        ;FLD1 & " IS NULL "
                  ;VAR4 = "" and CloseMatch = 1                                                                            // if the LookupValue is empty "", and CloseMatch is TRUE, then we will return "#N/A" (because "" has no close match)
                        ;"1=0"
                  ;TYP1 = "Text" // if the lookup field is a text field ...
                        ;"UPPER(" & FLD1 & ")"                                                                                     // to allow this function to be non-case-sensitive, we must uppercase all values in the lookup field
                        & If ( CloseMatch ; " <= " ; " = " )                                                                    // if CloseMatch is True, we will search for an approximate match (not an exact match) 
                        & "UPPER('" & GetAsText ( VAR4 ) & "')"                                                            // matching text must be enclosed in single quotes for SQL
                  ;TYP1 = "Date" // if the lookup field is a date field ...
                        ;FLD1
                        & If ( CloseMatch ; " <= " ; " = " )                                                                    // if CloseMatch is True, we will search for an approximate match (not an exact match) 
                        & "DATE " & "'"                                                                                                // format as ISO date for SQL comparison
                        & Year ( GetAsDate ( VAR4 ) ) & "-"
                        & Right ( "0" & Month ( GetAsDate ( VAR4 ) ) ; 2 ) & "-"
                        & Right ( "0" & Day ( GetAsDate ( VAR4 ) ) ; 2 ) & "'"
                  // else - if the lookup field is anything else ...                                                      // Note: this could be expanded to handle other types of fields (Time, Timestamp or Container)
                        ;FLD1
                        & If ( CloseMatch ;" <= " ; " = " )                                                                     // if CloseMatch is True, we will search for an approximate match (not an exact match) 
                        & GetAsNumber ( VAR4 )                                                                                 // matching number must never be enclosed in single quotes for SQL (causes type mismatch)
               ) // end case 1
              & Case (
                     CloseMatch ; " ORDER BY " & FLD1 & " DESC"                                                     // if CloseMatch is True, we MUST sort using the search field so we can return the nearest 'less-than' match
                    ;VAR4 = "" ; " ORDER BY " & FLD2 & " ASC"                                                         // if CloseMatch is False and the user searched for an empty value, we will sort using the return field
                    ; ""                                                                                                                     // otherwise, we don't need to sort at all (this slows things down, so it nice to skip this when we can)
                 ) // end case 2
              & " FETCH FIRST 1 ROW ONLY"                                                                                 // we only want to return 1 value (even if there were duplicates in the SORT ORDER)
        ;RESULT2 = ExecuteSQL ( MyCmd2 ; "" ; "" )
     ];
     Case (
        RESULT2 = "?"; "SQL Error (results): " & MyCmd2                                                            // if the SQL query caused an error, show the syntax (this is the query the user asked for)
        ;RESULT1 = "?"; "SQL Error (prefetch): " & MyCmd1                                                          // if the SQL query caused an error, show the syntax (this is the prequery to get the count)
        ;RESULT1 = 0 ; "#N/A"                                                                                                    // if no (close or exact) match was found, return "#N/A" (just like Excel does)
        ;RESULT2
     ) // end case
) // end function

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

		Julian Feb 18, 2021
Hi Doug, excited to find this function. I am trying to find the closest match for dates (TPtable:date) (about 300 records) with (HLtable:HLdate) (about 60 records) which is related to TPtable. I have inserted VLookupClassic in TPtable:cClosestMatch as a calculation: VLookupClassic ( HLtable::HLdate ; date ; cClosestMatch ; "True" ). It returns "?". Would be grateful for advice. I use FM 18. Thanks

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

VLookupClassic ( LookupValue ; SearchTableAndFieldName ; ReturnFieldName ; RangeLookup )

Comments

Support this website.

Support this website.