← 30 Days Excel Series
Day 6 of 30IntermediateLookup Functions

Day 6: INDEX & MATCH

5 questions · Excel Interview Preparation

Q1

Explain how INDEX works on its own.

INDEX(array, row_num, [col_num]) returns the value at a specific position in a range. =INDEX(A1:A100, 5) returns the 5th value in column A. =INDEX(A1:D100, 5, 3) returns the value in row 5, column 3 of the range A1:D100. By itself, INDEX is useful for extracting a specific item from a list. Combined with MATCH, it becomes the most powerful lookup tool in Excel.

💡 Interview tip: Understand INDEX alone before combining it with MATCH — the combination makes more sense when you understand each part.
Q2

Explain how MATCH works on its own.

MATCH(lookup_value, lookup_array, [match_type]) returns the POSITION of a value in a range — not the value itself. =MATCH("Delhi", A1:A100, 0) returns the row number where "Delhi" first appears. match_type 0 = exact match (always use this for data lookups). match_type 1 = find largest value less than or equal to lookup (requires ascending sort). match_type -1 = find smallest value greater than or equal to lookup (requires descending sort).

💡 Interview tip: MATCH returns a position (number). INDEX uses that position to return a value. Together: MATCH tells INDEX where to look.
Q3

Write an INDEX-MATCH formula to look up a customer name by CustomerID.

Assume CustomerID is in column B and CustomerName is in column A of your lookup table: =INDEX(A2:A100, MATCH(E2, B2:B100, 0)). This reads: find the position of E2 in column B, then return the value at that position from column A. Unlike VLOOKUP, the return column (A) is to the LEFT of the lookup column (B) — this is not possible with VLOOKUP.

💡 Interview tip: Always use 0 as the third argument of MATCH for exact match. Forgetting it defaults to 1 (approximate) which gives wrong results on unsorted data.
Q4

How do you do a two-criteria lookup using INDEX-MATCH?

Use an array version that checks two conditions simultaneously: =INDEX(C2:C100, MATCH(1, (A2:A100=F2)*(B2:B100=G2), 0)). In Excel 365, this works without Ctrl+Shift+Enter. In older Excel, press Ctrl+Shift+Enter to enter as an array formula. This returns the value from column C where both column A matches F2 AND column B matches G2. More readable alternative in Excel 365: use XLOOKUP with multiple criteria.

💡 Interview tip: This is a senior-level question. If asked, explain that you are multiplying two TRUE/FALSE arrays — 1×1=1 only when both conditions are met.
Q5

What is the advantage of INDEX-MATCH over VLOOKUP in a large dataset?

Three practical advantages: (1) No column direction restriction — can return left, right, up, or down. (2) Column references do not break when columns are inserted — VLOOKUP uses a number like col_index_num=3, which shifts if a column is added before it. INDEX-MATCH references the actual column. (3) Slightly faster on very large datasets because MATCH searches only one column rather than loading the entire table_array. The most important advantage in real work is the flexibility — the performance difference is noticeable only on hundreds of thousands of rows.

💡 Interview tip: Make sure you can articulate the column-insertion fragility of VLOOKUP — this is the most compelling real-world reason to prefer INDEX-MATCH.
← Day 5All DaysDay 7 →

Want 1:1 Excel coaching?

Join EVIKA Academy for live Excel training, real projects, and placement support in Delhi NCR.

Book Free Demo →
Best Data Analytics Course in Noida Delhi NCR | EVIKA ACADEMY