Returns a value from a result column corresponding to a match found in the given lookup column.
TableColumnLookup( [Lookup], [Lookup Column], [Result Column], [Not Found Value], [Match Mode], [Search Mode] )
Where:
Lookup is the value to be found.
Lookup Column is the single column table within which the value is to be found.
Result Column is the number of the column containing the data to be searched for.
Not Found Value (optional) is the value to be returned when match type is set to "EXACT" and an exact match cannot be found in the table. If this value is not provided, a #VALUE! error will be returned. (Note: An empty string is considered a value, and will prevent a #VALUE! error from being returned.)
Match Mode (optional) defines how the match will be determined. Choose from the following options:
The function will look for an exact match for the value. If no exact match is found, the Not Found Value will be returned. If this value is not provided, a #VALUE! error will be returned.
The function will search the entire lookup column for the value that is closest to the Lookup value. If two values are equidistant from the Lookup Column (ex. the Lookup value is 50 and both 40 and 60 are in the Lookup Column), then the function will return the first value found based on the Search Mode.
The function will search the entire lookup column for the value that is closest to, while still smaller than or equal to, the Lookup value. If the same value appears multiple times in the Lookup Column, then the function will return the first value found based on the Search Mode.
The function will search the entire lookup column for the value that is closest to, while still larger than or equal to, the Lookup value. If the same value appears multiple times in the Lookup Column, then the function will return the first value found based on the Search Mode.
Search Mode (optional) determines the direction that the search will proceed. This controls the value that is returned when multiple equally matching results are found. Choose from the following options:
The matching value closest to the top of the Lookup Column will be used.
The matching value closest to the bottom of the Lookup Column will be used.
Case Sensitivity
Text matching is not case sensitive (even when using the "EXACT" match mode).
Ensure the data (in the Lookup field and also in the Table being searched) can be matched.
Spaces and non-printable characters are common causes of data mismatch.
This can happen frequently when table data is copied from another source, like a database table.
If the function returns a #VALUE! error, a data mismatch could be the cause.
See #VALUE! below for more information.
Rule | Meaning |
|---|---|
TableColumnLookup( CarColorComboBoxReturn, TableSelectColumns(DWLookupVehicles, 3), TableSelectColumns(DWLookupVehicles, 2), "Color Not Available" ) | The function looks in column three of the Vehicles table to match the value returned from the CarColourComboBox and returns the corresponding car model.
If a match is found, the function returns the value from the third column of the Vehicles table in the same number row as the match found. If the value returned from the CarColourComboBox is not in the third column of the table, the function will return the string "Color Not Available". In this case, the Car Color selected in the combo box is "Blue", so the function returns "Golf". |
Rule | Meaning |
|---|---|
TableColumnLookup( DWVariableTargetAge, DWCalcPeople.Age, DWCalcPeople.Name, , "NEAREST", "FORWARD" ) | This example leverages the ability to reference a Columnar Range in a calculation table.
The function looks in the Age column of the People calculation table to match the value in the TargetAge variable and finds the name of the person that is closest in age. Once the nearest match is found, the function then looks in the same number row in the Name column of the People table and returns the value (ex. If the third row down contains the closest value in the Age column, the value in the third row down in the Name column will be returned). The user has not provided a Not Found Value, so in the event that the Age column is empty, a #VALUE! error will be returned. The "NEAREST" match mode means that the function will find the nearest value, whether it’s larger (i.e. an older person) or smaller (i.e. a younger person). And the FORWARD search mode indicates that in the case of multiple ages that are the same (ex. two people of age 42) or two values that are equally near the target age (ex. with a target age of 40, one person two years older at 42 and one person two years younger at age 38), the function will choose the value closest to the top of the table (i.e. first encountered when searching forward, from the top to bottom). In this case, the TargetAge in the variable is 38, so the function returns "Joe Bloggs", who is age 37. |
| Lookup | Lookup Column | Result Column | Not Found Value | Match Mode | Search Mode | Outcome |
|---|---|---|---|---|---|---|
| "Blue" | TableSelectColumns(DWLookupVehicles, 3) | TableSelectColumns(DWLookupVehicles, 2) | "None" | "EXACT" | FORWARD | "Golf" |
| "White" | TableSelectColumns(DWLookupVehicles, 3) | TableSelectColumns(DWLookupVehicles, 1) | "None" | "EXACT" | "FORWARD" | "Renault" |
| "Green" | TableSelectColumns(DWLookupVehicles, 3) | TableSelectColumns(DWLookupVehicles, 1) | "None" | "EXACT" | "FORWARD" | "None" |
| "Bleu de France" | TableSelectColumns(DWLookupVehicles, 3) | TableSelectColumns(DWLookupVehicles, 1) | "None" | "NEAREST" | "FORWARD" | "Mazda"
See Matches and Nearest Matches - Strings below to learn why Mazda is returned. |
| "Mazerati"
Intentionally misspelled for the purpose of the example. | TableSelectColumns(DWLookupVehicles, 1) | TableSelectColumns(DWLookupVehicles, 3) | "None" | "NEAREST" | "FORWARD" | "Black"
See Matches and Nearest Matches - Strings below to learn why Black is returned. |
| 43 | DWCalcPeople.MemberID | DWCalcPeople.Name | "NEAREST" | "FORWARD" | "Dave Sharp" | |
| 43 | DWCalcPeople.MemberID | DWCalcPeople.Name | "NEAREST" | "REVERSE" | "Joe Bloggs" | |
| 42 | DWCalcPeople.Age | DWCalcPeople.Name | "EXACT" | "FORWARD" | "Sandra Shield" | |
| 42 | DWCalcPeople.Age | DWCalcPeople.Name | "EXACT" | "REVERSE" | "Isabelle Jones"" |
Vehicles Table
| Make | Model | Colour |
|---|---|---|
| Volkswagen | Golf | Blue |
| Ford | Escort | Red |
| Renault | Kangoo | White |
| Mazda | 2 | Black |
| Maserati | GranTurismo | Silver |
People Calculation Table
| Member ID | Name | Age | Gender |
|---|---|---|---|
| 42 | Dave Sharp | 25 | Male |
| 44 | Joe Bloggs | 37 | Male |
| 96 | Sandra Shield | 42 | Female |
| 107 | Thomas Knight | 21 | Male |
| 251 | Isabelle Jones | 42 | Female |
This error occurs when the value to be found does not exist in the table and a Not Found Value was not specified.
Using the Make column of the Vehicles Table (above) as the Lookup Column that the value is to be found in, examples of this include:
"Tesla" does not exist in the table.
to resolve:
Modify the table to include the missing value.
"Volkwagon" is spelled incorrectly and does not exist in the table.
Similarly the entry in the table could have the incorrect spelling.
to resolve:
Ensure spellings are correct in the value filed and table values.
"Ford " has a space at the end, where the table value does not.
Similarly the entry in the table could have the extra space.
To resolve:
Ensure trailing spaces are removed.
Consider using the Trim function to remove whitespace characters.
The Trim functions will also remove additional spaces in between words.
Finding and cleaning offending characters using a third party editor is recommended.
Notepad++ allows all characters to be shown.
Paste the data into Notepad++ and select View>Show Symbol>Show All Characters
Remove all non printable characters and copy the data back into DriveWorks.
When considering how to search for a match, DriveWorks will take into consideration the data type of the value passed into the lookup.
If the value passed into the lookup is a number, then DriveWorks will try to evaluate the values in the table as numbers for comparison purposes.
If the value being evaluated in the table cannot be converted into the same type as the value passed into the lookup, the table value will be considered non-matching.
When considering nearest matches for numeric values, DriveWorks will search the entire table column for the number that is closest to the Value in the first parameter of the lookup.
This behavior differs from the method that Microsoft Excel’s lookup functions employ.
With Excel’s range lookup approach for a nearest match, the table is searched until a value that is greater than the target value is found, and the last value less than the target value is returned.
With a table that is sorted, this means that you will always receive a value that is less than the target value.
DriveWorks searches the entire table, and will always return the closest value based on the match mode specified.
Use "NEAREST_LARGER" or "NEAREST_SMALLER" when your rules include a less than or greater than requirement.
Use "NEAREST" when you want the closest value, regardless of whether it is less than or greater than the target value.
In the case where two values are equidistant from the search Lookup value, DriveWorks will return the first occurrence that it encounters, using the Search Mode to choose either the cell closest to the top of the table ("FORWARD") or closest to the bottom of the table ("REVERSE").
DriveWorks compares dates in the same way, provided both the Value parameter, and the data in the table are formatted as dates.
When using the "NEAREST" Match Mode, DriveWorks will find the date in the table that is closest to the Lookup value in the first parameter.
This date may be before or after the provided Lookup value.
Use "NEAREST_LARGER" to find the next later (future) date and "NEAREST_SMALLER" to find the nearest earlier (past) date.
If DriveWorks finds two dates equidistant from the Lookup value parameter, the returned match will be the value closest to the top of the table (for Search Mode "FORWARD") or closest to the bottom of the table (for Search Mode "REVERSE").
When comparing strings, DriveWorks ignores case and tries to match the characters starting at the beginning of the string.
The nearest match is determined by the number of characters at the beginning of the string that match the Value parameter.
When multiple cells in the table column match the same number of characters at the beginning of the string, DriveWorks will do a nearest match to the first non-matching character in the string.
When we look at the examples above, we can see that searching the Make column of the Vehicles table for "Maserati" will return "Silver" from the Color column.
But, because we misspelled "Mazerati" in our example above, DriveWorks finds that "Mazda" has the first three letters matching our misspelled input value, while "Maserati" only shares the first two characters in common.
Therefore, DriveWorks returns "Black" from the Color column as the nearest match.
The fact that the remainder of "Mazerati" matches "Maserati" is not considered.
"NEAREST_LARGER" will match letters later in the alphabet or ASCII table (ex. Z is considered larger than A) and "NEAREST_SMALLER" will match letters earlier in the alphabet or ASCII table (ex. A is considered smaller than Z).
Combining this with the matching of the beginning of the string, "NEAREST_LARGER" would match "Matco" to "Mazda" because the "Ma" match and "Maz" is the next larger string found to "Mat".
But "NEAREST_SMALLER" would match "Matco" to "Maserati" because the "Ma" match and "Mas" is the next smaller string found to "Mat".