說明
Use this function to search for a value in the first column of a table range and return a value in the same row from another specified column. Supported in Chains.
VLOOKUP is useful for finding information in large datasets, such as looking up prices in a product catalog or finding employee details in a database.
語法
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
lookup_value |
是 | The value to search for in the first column of the table array. | A cell reference, a cell range, a number, a text string, or a formula which results in any of these. |
table_array |
是 | The table of information in which data is looked up. | A cell range. |
col_index_num |
是 | The column number in table_array from which the matching value should be returned. |
A positive integer, a cell reference containing a positive integer, or a formula which results in either of these. 1 = the first column in table_array. |
range_lookup |
沒有 | Specifies whether you want VLOOKUP to find an exact match or an approximate match. | TRUE (default) for approximate match, FALSE for exact match. |
範例
範例資料
| A | B | C | 暗示性的對話 (D) |
|---|---|---|---|
| Product ID | Product Name | 類別 | 價格 |
| 1001 | Widget A | Gadgets | $10.99 |
| 1002 | Widget B | Gadgets | $15.99 |
| 1003 | Gizmo X | Electronics | $25.50 |
| 1004 | Gizmo Y | Electronics | $30.00 |
| 1005 | Gizmo Z | Electronics | $37.50 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=VLOOKUP(1002, A2:D6, 2 ,FALSE) |
Looks up Product ID "1002" and returns the corresponding Product Name. | Widget B |
=VLOOKUP(1003, A2:D6, 4, FALSE) |
Looks up Product ID 1003 and returns the corresponding Price. | 25.50 |
=VLOOKUP("Gizmo X", B2:D6, 2, FALSE) |
Looks up "Gizmo X" in the second column and returns the corresponding Category. | Electronics |
=VLOOKUP(1006, A2:D6, 2, TRUE) |
Looks up Product ID 1006 (which doesn't exist) and returns the next lowest value. | Gizmo Z |
=VLOOKUP(1002, A2:D6, 2) |
Looks up Product ID 1002, with range_lookup argument omitted (defaults to TRUE). |
Widget B |
附註
- The lookup value must be in the first column of the
table_arrayfor VLOOKUP to work correctly. - If
range_lookupis TRUE or omitted, the first column oftable_arraymust be sorted in ascending order. - If
range_lookupis FALSE and the lookup_value is not found, VLOOKUP returns #N/A error. - VLOOKUP is not case-sensitive when comparing text values.
- For better performance with large datasets, consider using INDEX and MATCH functions instead of VLOOKUP.
提示
- Use FALSE for
range_lookupwhen you need an exact match, especially with unsorted data or text values. - Combine VLOOKUP with other functions such as IFERROR to handle cases where the lookup value is not found.
- For reverse lookups or when your lookup column is not the leftmost, consider using INDEX and MATCH instead.
- When working with large datasets, using VLOOKUP with
range_lookupset to TRUE can improve performance if your data is sorted. - Remember that VLOOKUP can only return values from columns to the right of the lookup column in the
table_array.