Function Problems

Use INDEX and MATCH Instead of Nested IFs in Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

Question details

Return QA questions from a second worksheet based on a call type selected from a drop-down list, without using a large nested IF formula.

Use INDEX and MATCH Instead of Nested IFs in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has a call-type drop-down, and another worksheet contains call types and related QA content.
Observed behavior
The desired result is a list of questions corresponding to the selected call type.
Before you start

Confirm the drop-down cell and the source columns for call types and questions. The INDEX and MATCH example assumes the call type is in Sheet2 column M and its questions start one row below the matching cell in column A.

Solution 1Recommended

Use INDEX and MATCH to return questions below a call type

Use this approach when each call type identifies the start of a block of questions on the source worksheet.

MATCH finds the first exact match for the selected call type. INDEX returns a value from column A, and the row offset increases as you copy the formula down.

1
Select a call type

Choose a call type from the drop-down in cell C6. If your drop-down is elsewhere, replace $C$6 in the formula with its cell reference.

2
Enter the lookup formula

In the first question cell, enter =INDEX(Sheet2!$A:$A,MATCH($C$6,Sheet2!$M:$M,0)+ROW(A1)). Replace Sheet2 or the column references if your source layout differs.

3
Copy the formula down

Drag the formula cell's fill handle down through the required question cells. ROW(A1) changes to ROW(A2), ROW(A3), and so on, returning successive rows after the match.

4
Check the returned questions

Change the selection in C6 and verify that the questions belong to that call type. If the first question is on the matched row rather than the row below it, use +ROW(A1)-1 as the offset.

Use INDEX and MATCH to return questions below a call type
Limit the returned rows: The formula does not detect the end of a question block. Copy it only as far as needed, or it may return content from a following block. A missing exact match returns #N/A.
Work with call-type questions in WPS

Look up call-type questions in WPS Spreadsheet

Use INDEX and MATCH in WPS Spreadsheet to pull questions from a source sheet based on a drop-down selection, then fill the formula down for successive questions.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select the workbook containing the call-type drop-down and source data.
  2. 2. Check the source layout: Open Sheet2 and confirm that call types are in column M and questions begin one row below each matching call type in column A.
  3. 3. Enter the formula: On the output sheet, select the first question cell and enter =INDEX(Sheet2!$A:$A,MATCH($C$6,Sheet2!$M:$M,0)+ROW(A1)). Adjust the sheet, columns, and C6 reference if needed.
  4. 4. Fill and test: Drag the formula cell's fill handle down for the required number of questions, then choose a call type in C6 and check the results.
Replace a long nested IF formula with an exact-match lookupCopy the lookup formula down to return successive question rowsOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the INDEX and MATCH formula return #N/A?

MATCH with 0 requires an exact match. Check that the value selected in C6 exists in Sheet2 column M and that the text does not contain unwanted spaces.

How do I return the question on the matched row?

Change the offset from +ROW(A1) to +ROW(A1)-1. The first formula then returns the value from the matched row in column A, and copies below it return subsequent rows.

Will INDEX and MATCH stop at the next call type?

No. The formula returns successive rows without checking block boundaries. Copy it only through the required question cells, or use a different layout and a suitable filtering formula.

Why does FILTER show a #SPILL! error?

The cells needed for the results may be occupied. Clear the output cells below the FILTER formula and try again.