Returns a value from a table corresponding to a match found in the first column.
Searches the first column in the selected table for a match (either exact or nearest; defined by MatchType) to the Lookup value. If a match is found, the data from the specified column in the matching row is returned.
VLookup([Value], [TableName], [ColumnIndex], [MatchType])
Where:
Value is the value to be found.
TableName is the table within which the Lookup value is to be found.
ColumnIndex is the number of the column containing the data to be returned.
MatchType defines whether the match has to be exact (False) or the nearest found (True).
Ensure the data (in the Value field and also in the Table being searched) can be matched.
Spaces and non-printable characters are common causes of data mismatch.
If the function returns a #VALUE! error, a data mismatch could be the cause.
See #VALUE! below for more information.
Rule | Meaning |
|---|---|
| VLookup(CarMakeComboBoxReturn, Vehicles, 2, FALSE) | The function looks in the Vehicles table for a match, in the first column of the table, to the value returned from the CarMakeComboBox. If a match is found, the function then looks across to the second column and returns the value in the same row as the match found. The user has also specified that the match needs to be exact (denoted by the FALSE term). In this case, the Car Make selected in the combo box is "Volkswagen", so the function returns "Golf". |
Value | TableName | ColumnIndex | MatchType | Outcome |
|---|---|---|---|---|
| "Volkswagen" | Vehicles | 2 | False (Exact) | "Golf" |
| "Mazda" | Vehicles | 3 | False (Exact) | "Black" |
| 43 | People | 2 | True (Nearest) | "Dave Sharp" |
| 251 | People | 3 | False (Exact) | 56 |
Vehicles Table
| Make | Model | Colour |
|---|---|---|
| Volkswagen | Golf | Blue |
| Ford | Escort | Red |
| Renault | Kangoo | White |
| Mazda | 2 | Black |
People 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 | 56 | Female |
This error often occurs when the value to be found does not exist in the table.
Using the Vehicles Table (above) as the table 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.