VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldNames )

Works similar to the Excel VLookup function (but with some nice optional features): Searches in a specific field for a matching lookup value, and returns the value from a different field in the same record. -- This version of the function is case-sensitive, and does not allow 'nearby' matches, but it can handle multiple matching values, and it can return multiple fields. If no match is found, it returns an empty string ''. Can pull data from any table (even from non-related tables). - Tested with FileMaker Pro v13 thru v19. Last updated June 30, 2022

  Average rating: 4.6 (28 votes) Log in to vote

Doug Staubach - Show more from this author https://www.linkedin.com/in/dougstaubach/

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

DESCRIPTION:

Works similar to the Excel VLookup function (but with some nice optional features): Searches in a specific field for a matching lookup value, and returns the value from a different field in the same record. -- This version of the function is case-sensitive, and does not allow 'nearby' matches, but it can handle multiple matching values, and it can return multiple fields. -- If no match is found, it returns an empty list ''. Can pull data from any table (even from non-related tables).

If you are looking for a funtion that works EXACTLY like Excel does (can find close matches, is non-case sensitive, and returns only one value), check out my other function "VLookupClassic"

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 both be enclosed in quotes like this ... "table::field" and "field"

If you plan on using a fieldname on the current layout for parameter 2, it is suggested that you do NOT enclose the fieldname in quotes, because FileMaker will "trigger" a recalculation when the value in that field changes.

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 v13 thru v19 (this might also work with FileMaker Pro v12 -- main requirement is the ExecuteSQL function)

Note: This function uses RECURSION to evaluate each item in the second parameter as a separate query (recursion is cool, but if you have a massively-large list of Matching Values, you could run out of memory).

INPUTS:
(1) The first parameter (LookupTableAndFieldName) must include both a tablename, and a fieldname, formatted like so: Table::Field
- If the fieldname is from an unrelated table, you must enclose the value in quotes like this ... "Table2::Field1"
- Multiple values are not allowed for this parameter (only one lookup table and one lookup field)
(2) The second parameter (MatchingValueOrList) can be static text enclosed in quotes like "Apple" or a list of values, like "Apple¶Banana¶Pear", or it can be a fieldname, formatted like so: Table::Field
- If you want to provide a fieldname for the second parameter, and the field is from the current layout table or a related table, then it does not have to be enclosed in quotes (recommendation is to not use quotes)
- If you want to provide a fieldname for the second parameter, and the field is NOT from the current layout table or a related table, then it must be enlosed in quotes like this "Table2::Field1"
(3) The third parameter (ReturnFieldNames) can be a single fieldname like "Price" or multiple fieldnames like "Price;Quantity;Description" or even "Price¶Quantity¶Description"
- Any fieldname that is specified in the third parameter will ALWAYS come from the same table as the first parameter (if any table name is included, it will be ignored)
- If you are specifying multiple field names), you must enclose the entire value in quotes like this ... "table1::field1" or "field1;field2;field3"

OUTPUTS:
(Note: This changed in v1.4) - Always returns a string value or an empty string. If the query finds multiple values for the same field, each value will be separated by the ¶ character (much like a FileMaker list). If multiple fields were requested, each field's contents will be separated by ";" characters. If no match was found, an empty string will be returned. -- Starting with version 1.4, there are no longer any trailing ¶ characters.

EXAMPLES:
Assume that the current layout table is TableInventory, and we have an unrelated table called TableCatalog
Example1: VLookup ( TableInventory::ItemNumber ; "101¶620¶" ; Description ) ... look up two item numbers from the local or related table TableInventory and returns two descriptions ... "Lamp¶Television"
Example2: VLookup ( "TableCatalog::Description" ; "Lamp" ; "TableCatalog:ItemID" ) ... search UNRELATED TableCatalog for description=Lamp (case-sensitive) and return ItemID ... "101"
Example3: VLookup ( "TableCatalog::Description" ; "LaMp" ; "TableCatalog:ItemID" ) ... search same TableCatalog for description=LaMp (case-sensitive = no match), so return empty string ... ""
Example4: VLookup ( TableInventory::ItemNumber ; List ( TableCatalog::ItemID ) ; TableInventory::Description ) ... for each item in the dynamic list, returns a description ... "Cigar¶Rum¶Recliner"
Example5: VLookup ( TableInventory::ItemNumber ; "101" ; "Description;Size;Cost" ) ... looks for matching item number, and returns three fields (separated by ";" marks) ... "Lamp;Large;25.99"

New in v1.2:
(1) Fixed an issue that became apparent with the upgrade to FileMaker v14 (no way to add unrelated tables to a function) - modified code to accept unrelated table and fields with quotes around them.
(2) The user can now specify multiple fields for the return values. This caused me to rename the third parameter from "ReturnFieldName" to "ReturnFieldNames" -- Multiple field values will be separated by ";" characters
(3) In this version, if the ExecuteSQL statement results in an error, an error message will be returned (instead of an empty string). The error will show the SQL query that failed (this can be very useful for troubleshooting).

New in v1.3
Trailing carriage returns ¶ have been removed.

New in v1.4
Really removed trailing carriage returns this time (thanks to feedback from Jose Martorell). Also made a couple of minor changes dues to differences in the ExecuteSQL function in Filemaker Pro versions 18 and 19.

New in v1.5
Another attempt at eliminating the carriage return problem (based on feedback from Carlo Vlekken)

(Let me know if you like it?)

Comments

		Nick, California Nov 18, 2015
This is exactly what I'm looking for. I am just not able to get it it work. I have two Tables (In House Jobs) & (Paper Catalog) The paper catalog is set up like an excel sheet. I have all my different papers I offer. I consolidated the sizes and names into one field so I can pull it from a dropdown menu over into the In House Jobs. When I select the paper in a drop down list on the In House Jobs Table I want it to pull the cost per sheet from the Paper Catalog into another field on that same table. Is this possible? I was doing it with Vlookup in excel without any problems. Thanks!

		RoYoMi, Kenosh Wi Feb 21, 2016
This is pretty cool function. There is a problem with that it always appends a paragraph character to the end of the string. To correct for this I modified the lines in the function as follows: Was: & "¶" & VLookup ( LookupTableNameAndFieldName ; RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ; ReturnFieldName ) // recursion (move to next item and go again) Corrected: & If ( RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ≠ "" ; "¶" & funVLookup ( LookupTableNameAndFieldName ; RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ; ReturnFieldName ) ; "" ) // recursion (move to next item and go again) This forces the function to test if recursion is necessary. If it is necessary then it also includes the paragraph mark.

		Doug Staubach Feb 22, 2016
Hi Nick: Yes, this formula will work. Your formula should be similar to the following: VLookup ( Paper_Catalog:Sizes_And_Names ; In_House_Jobs::Paper_Selected ; Paper_Catalog::Cost_Per_Sheet ) Note that table names are not needed for the second and third parameter, so the following formula would work just as well, and is a bit shorter: VLookup ( Paper_Catalog:Sizes_And_Names ; Paper_Selected ; Cost_Per_Sheet ) I included the table names in the first example, because you are working with two different tables, and I though it might be useful to illustrate which table contains which fields. Let me know if you have any other issues. Thanks, Doug

		Doug Staubach Feb 24, 2016
Hi RoYoMi: Thank you! - I appreciate your comments and suggestions. Your suggested fix to remove the trailing "¶" character might be useful for some people, but I'm not going to include it in the main code of the function (see below for an explanation). I purposely left the trailing "¶" characters as part of this function, to make it behave in the same way that all of FileMaker's built-in valuelist functions work (example: LeftValues, MiddleValues, RightValues, and FilterValues all do this). -- FileMaker says this is good for combining lists. I don't have a real preference, but since FileMaker does it that way, then I will also. Note: For those people who want to clean up the final result, you can always put this function inside of another function that is specifically designed to remove the last "¶" character from any text (there are several functions on this site that do this, including Super_Trim, Trim_CR, and TrimReturns). -- For example: Trim_CR ( VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldName ) ) Thanks, Doug

		Jack, OKC Jan 7, 2017
I love this formula and use it throughout my solution, but I noticed it does not appear to return any results when executed on an Android device via WebDirect. On all other platforms, WebDirect uses this function just fine. Your VLookupClassic function however, works as intended on Android devices. I cannot determine the difference but would like to keep using your this function, even though, in terms of features, both functions would work just fine.

		Doug Staubach Jun 20, 2017
Hello everyone: @Nick - Perhaps Android can't handle recursive functions? - Try with the last version 1.3 and see if it makes a difference. - Other than that, I'm not sure that I can help (I'm an iPhone user myself). @RoYoMi - I use this function quite a bit in my own work, and I started to grow weary of trimming the trailing carriage return ("¶" character), so I decided to remove it from version 1.3 - thanks for your patience. @Everyone - Let me know if this function was useful to you (I'd love to hear your stories and feedback!) Thanks, Doug

		Alexander Klemenow, Berlin Mar 24, 2018
Hi, Doug! Your function is great, thank you!

		Doug Staubach Jun 11, 2018
HI Alexander: Thank you for the nice comment - I'm glad that you like it! Regards, Doug

		Hubert Henle Jan 6, 2020
Hi Doug, this function works really nice for me - thanks for sharing! In my case "MatchingValueOrList" could contain single quotes which in turn throws an SQL error. I added a substitute to escape single quotes in the line setting TYP1 in case it's text: TYP1 = "Text" ; "'" & Substitute ( GetAsText ( VAR4 ) ; "'" ; "''" ) & "'" This solved the issue. Best regards, Hubert

		jwilson Jan 13, 2021
Just trying this function for the 1st time using FMP 17 on Windows. It throws an error on the LookupTableAndFieldName that says “This field comes from an unrelated table. Only global fields can be referenced in unrelated tables” Have I defined something wrong? Or does the function not like FMP17 ? Thanks for any guidance

		Doug Staubach Jan 20, 2021
Hi Hubert: Thank you for the valuable tip - Next time I modify the source code, I will implement this fix. Regards, Doug

		Doug Staubach Jan 20, 2021
Hi jwilson: Sorry, but I don't know the answer (I haven't upgraded to FMP 17 yet; I have been reluctant to do so because the company changed their name from FileMaker to "Claris" and right about the same time, the licensing models started to get expensive and complicated). I will say that it worked on FMP versions 13-15, and it intentionally bypasses the problem with tables having to be related to each other. - One "trick" worth trying is to create a "cartesian" relationship between the two tables (in the table design view, it looks like the letter "x"). Creating a "cartesian join" might help you get you past the error message, because it tricks the database into thinking that EVERY record in Table 1 matches EVERY record in Table 2. (In most cases that's too many matches to be useful, but it's pretty much prefect for VLOOKUP, becasue we are creating our own "filter" with the "MatchingValueOrList" parameter in our function.) For more information (from Claris) about Cartesian Joins, here is a link: https://support.claris.com/s/article/Using-the-Cartesian-Product-Relationship-in-FileMaker-Pro-1503692924110?language=en_US Please let the community know if this solved your problem (and allowed VLOOKUP to work). Thanks, Doug

		Jose Martorell, MD Baja Jun 21, 2021
Hello Dough, thank you for your function. It has proven to be very useful for me in several unrelated tables lookup operations. I've noticed that in 1.3 you dropped the carriage return at the very end of the result (or results), however, using the function as it is right now, it always delivers one when evaluated, whether it finds a single match, multiple, or none at all. I made it stop returning a carriage at the end by removing the "¶" & at the final ValueCount which I assume is the call for the recursion. It seems to work OK with that little modification now.

		Doug Staubach Jul 28, 2021
Hi Hubert Henle: I have incorporated your fix (for escaping single quotes embedded inside text) into version 1.4 - Thank you for suggesting it!

		Doug Staubach Jul 28, 2021
Hi Hubert Henle: I have incorporated your fix (for escaping single quotes embedded inside text) into version 1.4 - Thank you for suggesting it!

		Doug Staubach Jul 28, 2021
Hi jwilson: I just tried this custom function on FMP v19, and it worked fine WITHOUT having to create a cartesian join. - BUT, when you are going to use an unrelated tablename and fieldname for this function, you have to put them in quotes, like this: "Table2:: Field2"

		Doug Staubach Jul 28, 2021
HI Jose Martorell: I have included your fix (to get rid of trailing carriage return) in version 1.4 of the function. I then tested it with FMP v19, and everything works as intended. - Thank you for your suggestion!

		Carlo Vlekken, Vlekken Consult BV Jun 24, 2022
Hi Doug, I tested your CF version 1.4 on FMP v19, but apparently when asking for a MatchingList instead of a MatchingValue it returns the list without the carriage returns in between the list values. In order to solve this, I changed in one of the last lines " ; VLookup ( LookupTableAndFieldName ; RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ; ReturnFieldNames )" into " ; ¶ & VLookup ( LookupTableAndFieldName ; RightValues ( MatchingValueOrList ; ValueCount ( MatchingValueOrList ) - 1) ; ReturnFieldNames )".

		Doug Staubach Jun 30, 2022
Hi Carlo Vlekken: Thanks for the feedback. - I have incorporated your edit into the current version (1.5)

		NikK Nov 17, 2022
Is it possible to concatenate text with LookupTableAndFieldName to create the search string? Eg “TOTAL “&TableName::FieldName&”00-0000” I can’t make this work?

		NikK Nov 17, 2022
Is it possible to concatenate text with LookupTableAndFieldName to create the search string? Eg “TOTAL “&TableName::FieldName&”00-0000” I can’t make this work?

		NikK Nov 17, 2022
Is it possible to concatenate text with LookupTableAndFieldName to create the search string? Eg “TOTAL “&TableName::FieldName&”00-0000” I can’t make this work?

		NikK Nov 18, 2022
Is it possible to concatenate text with LookupTableAndFieldName to create the search string? Eg “TOTAL “&TableName::FieldName&”00-0000” I can’t make this work?

		NikK Nov 18, 2022
Is it possible to concatenate text with LookupTableAndFieldName to create the search string? Eg “TOTAL “&TableName::FieldName&”00-0000” I can’t make this work?

		NikK Nov 18, 2022
Sorry for the repeat posts. I don’t know why that is happening.

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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: