Description
Searches a specified Excel file for a lookup key and returns the related value.
Syntax
XLSLOOKUP ( xlsFile, search_value, search_range, search_col )
| Parameter | Description |
|---|---|
| xlsFile | The path (using Universal Naming Convention — computer name and a share name) and name for the Excel spreadsheet containing the lookup values. |
| search_value | A value, text string, or reference to a cell containing a value that is matched against data in the first column of search_range. |
| search_range | The reference of the range (table) to be searched. The cells in the first column of search_range can contain numbers, text, or logical values. The contents of the first column must be in ascending order (for example, -2, -1, 0, 2...A through Z, False, True). Text searches are not case-sensitive. |
| search_col | The column in the search range from which the matching value is returned. Search_col can be a number from 1 to the number of rows in the search range. If search_col is less than 1, #VALUE! is returned. If search_col is greater than the number of rows in the table, #REF! is returned. |
Remarks
XLSLOOKUP compares the information in the first column of search_range to the supplied search_value. When a match is found, information located in the same row and supplied column (search_col) is returned. If search_value cannot be found in the first column of search_range, the function returns #NULL!.
Note: XLSLOOKUP supports .xls (Excel 97 — 2003) Workbook files only. To use an Excel file from a later release of Excel (that is, with an .xlsx extension), use the Excel Save As command to save as type: Excel 97 — 2003 Workbook (*.xls).
Example
| Parameter | Column A | Column B | Column C | Column D |
|---|---|---|---|---|
| Row 1 | Employee | StartDate | Emp. No. | Exempt |
| Row 2 | Anderson | 10/15/84 | 2348 | Y |
| Row 3 | Clark | 2/6/90 | 4891 | N |
| Row 4 | Davis | 6/21/80 | 2480 | Y |
| Row 5 | Lee | 4/20/88 | 3793 | Y |
This function returns N:
XLSLOOKUP("c:\employees\empdata.xls","Clark","A2:D5",4)
This function returns 3793:
XLSLOOKUP(c:\libraries\documents\mydocuments\employees\empdata.xls","Lee","A2:D5",3)
Troubleshooting tip
If the pathname to the Excel file is incomplete or not correct, you will see the following error in your transaction log when you run the DTS file:
Step resulted in Successful Termination — data type mismatch
You may see this error if your computer has been upgraded to a newer operating system, because the default pathname may have changed.