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.
Связанные формулы
Нужна другая формула?
Опишите, что вам нужно, обычным языком — и наш ИИ создаст формулу за вас.
Попробовать Генератор формул