Скачать или смотреть HOW TO USE INDEX & MATCH FORMULA IN EXCEL

HOW TO USE INDEX & MATCH FORMLA IN EXCEL
To use the INDEX MATCH formula in Excel, you combine the INDEX function, which returns a value from a given range, with the MATCH function, which finds the position of a value in a range. The MATCH function identifies the row or column number for the INDEX function, which then uses that position to retrieve the desired output. The general formula is =INDEX(return_array, MATCH(lookup_value, lookup_array, 0)), where 0 ensures an exact match.

Understanding the Functions

INDEX(array, row_num, [column_num]):
This function returns the value at the intersection of a specified row and column within a range (array).

MATCH(lookup_value, lookup_array, [match_ty

1. Identify your goal:
Decide what value you want to find and what criteria you will use to look it up.
2. Write the MATCH function first:
Determine the row or column number for your lookup.

Example: To find the position of "Apple" in column A: =MATCH("Apple", A:A, 0).

3. Write the INDEX function around the MATCH function:
Tell the INDEX function where to find the data and use the MATCH result as the row or column number.

Example: To get the value from column C where column A is "Apple": =INDEX(C:C, MATCH("Apple", A:A, 0)).

4. Press Enter
to see the result.

Why Use INDEX MATCH?

Flexibility:
Unlike VLOOKUP, INDEX MATCH can look up values to the left of the lookup column, right, up, or down.
Resilience:
It's not affected by the insertion or deletion of columns, making it more stable than VLOOKUP.
Efficiency:
On large datasets, INDEX MATCH can be faster than VLOOKUP because it only references the specific columns needed.