Advanced Lookups: INDEX & MATCH

While VLOOKUP is famous, it has major limitations (it can only look to the right and breaks if you insert columns). For advanced financial reporting, experts use the combination of INDEX and MATCH.

1. The MATCH Function (Finding Position)

The MATCH function does not find a value; it finds the position (row or column number) of a value in a list.

• Syntax: =MATCH(lookup_value, lookup_array, 0)
• Example: If you search for "Cherry" in a list of [Apple, Banana, Cherry], MATCH returns 3.

2. The INDEX Function (Retrieving Value)

The INDEX function is the opposite. You tell it the coordinates (Row and Column number), and it gives you the value in that cell.

• Syntax: =INDEX(range, row_num, [col_num])
• Example: =INDEX(A1:A10, 3) returns whatever is in the 3rd row of that range.

3. Combining INDEX & MATCH

By nesting MATCH inside INDEX, you create a search engine that is far more powerful than VLOOKUP.

The Logic:

1. MATCH finds where (which row) the data is.
2. INDEX goes to that row and retrieves the data.

The Master Formula:

=INDEX(column_with_answer, MATCH(search_value, column_with_search_item, 0))

4. Why INDEX-MATCH is better than VLOOKUP

Loading comparison…

Exam Pattern Questions and Answers

Question 1: "Explain why a student should learn INDEX-MATCH instead of just relying on VLOOKUP." (6 Marks)

Answer: A student should learn INDEX-MATCH because it overcomes three critical limitations of VLOOKUP:

1. Leftward Lookup: VLOOKUP can only search in the first column and return values to the right. If the unique ID is in the middle and you need data from a column to its left, VLOOKUP fails. INDEX-MATCH can search any column and return data from any other column.
2. Structural Flexibility: VLOOKUP requires a hard-coded column index number (e.g., 3). If a user inserts a new column into the table, the formula will point to the wrong data. Since INDEX-MATCH uses cell ranges instead of numbers, it adjusts automatically.
3. Processing Speed: For very large datasets, VLOOKUP can slow down the file because it looks at the entire table range. INDEX-MATCH only looks at the two specific columns involved, making it significantly faster.

Question 2: "Write the syntax for the INDEX function and explain its arguments." (4 Marks)

Answer: Syntax: =INDEX(array, row_num, [column_num])

Arguments:

1. array: This is the range of cells or the table from which you want to retrieve information.
2. row_num: This specifies the vertical position (row number) within the array from which to return a value.
3. column_num (Optional): If the array has multiple columns, this specifies the horizontal position from which to return a value.

Summary

• MATCH finds the Index/Position.
• INDEX returns the Value at a given position.
• INDEX-MATCH is the "Professional" way to do lookups.
• Use it to perform Left Lookups and build Static-Proof formulas.

Quiz Time! 🎯

Loading quiz…