INDEX-MATCH Function
A powerful combination of INDEX and MATCH functions that creates flexible lookups in any direction. More versatile than VLOOKUP and often preferred by advanced users.
ExcelGoogle Sheets
语法
INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))参数
| 参数名 | 说明 |
|---|---|
| array | The range from which to return a value (INDEX). |
| lookup_value | The value to find (MATCH). |
| lookup_array | The range to search in (MATCH). |
| match_type(可选) | 0 = exact match, 1 = largest value ≤ lookup, -1 = smallest value ≥ lookup. |
示例
Look up a price by product name
=INDEX(B2:B10, MATCH("Apple", A2:A10, 0))结果: 1.50
Two-way lookup (row and column)
=INDEX(B2:D10, MATCH("Alice", A2:A10, 0), MATCH("Q2", B1:D1, 0))结果: Returns the value at the intersection of Alice's row and Q2's column
Left lookup (impossible with VLOOKUP)
=INDEX(A2:A10, MATCH("Marketing", C2:C10, 0))数据: Column A = IDs, Column C = Departments
结果: 1001
技巧与最佳实践
- •Always use 0 as the match_type for exact matches.
- •INDEX-MATCH can look up values to the left, unlike VLOOKUP.
- •This combination is more resistant to column insertions/deletions than VLOOKUP.
- •For two-dimensional lookups, nest two MATCH functions inside INDEX.
- •Works in all versions of Excel and Google Sheets.
相关公式
需要其他公式？
用自然语言描述您的需求，AI 将为您生成公式。
试试公式生成器