Returns a value from a table corresponding to a match found in the first row.
Searches the first row 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 row in the matching column is returned.
HLOOKUP([Value], [TableName], [RowIndex], [MatchType])
Where:
Value is the value to be found.
TableName is the table within which the Lookup value is to be found.
RowIndex is the number of the row 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.
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 |
|---|---|
| HLOOKUP(CarMakeComboBoxReturn, DwLookupVehicles, 2, FALSE) | The function looks in the Vehicles table for a match, in the first row of the table, to the value returned from the CarMakeComboBox.
If a match is found, the function then looks down to the second column and returns the value in the same row as the match found. The rule also specifies 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" |
| "Mazerati"
Intentionally misspelled for the purpose of the example. | Vehicles | 3 | False (Exact) | #VALUE! |
| "Mazerati"
Intentionally misspelled for the purpose of the example. | Vehicles | 3 | True (Nearest) | "Black"
See Matches and Nearest Matches - Strings below to learn why Black is returned. |
| 51 | People | 2 | True (Nearest) | "Joe Bloggs"
See Matches and Nearest Matches - Numeric below to learn why Joe Bloggs is returned. |
| 251 | People | 3 | False (Exact) | 56 |
| 104 | People | 2 | True (Nearest) | "Thomas Knight" |
Vehicles Table
| Make | Volkswagen | Ford | Renault | Mazda | Maserati |
|---|---|---|---|---|---|
| Model | Golf | Escort | Kangoo | 2 | GranTurismo |
| Colour | Blue | Red | White | Black | Silver |
People Table
| Member ID | 42 | 49 | 96 | 107 | 251 |
|---|---|---|---|---|---|
| Name | Dave Sharp | Joe Bloggs | Sandra Shield | Thomas Knight | Isabelle Jones |
| Age | 25 | 37 | 42 | 21 | 56 |
| Gender | Male | Male | Female | Male | 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.
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, whether it is less than or greater than the target value.
In the case where two values are equidistant from the search Value, DriveWorks will return the first occurrence, the cell closest to the left edge of the table.
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 method (MatchType is TRUE), DriveWorks will find the date in the table that is closest to the Value in the first parameter.
This date may be before or after the provided Value.
If DriveWorks finds two dates equidistant from the Value parameter, the returned match will be the value closest to the left edge of the table.
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 Vehicles table for "Maserati" will return "Silver" from the color row.
But, because we misspelled "Mazerati" in our second example, 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 row as the nearest match.
The fact that the remainder of "Mazerati" matches "Maserati" is not considered.