docs / articles / Index Match Blank: Return Blank

Index Match Blank: Return Blank

Sara Jul 09, 2026 2026-07-09 04:40:45

When working with spreadsheets, you often encounter situations where you need to retrieve data based on certain conditions. One common scenario is using the INDEX MATCH function to pull data from a table, but what happens when the cell you're trying to match is blank? In this article, we'll explore how to handle blank cells in INDEX MATCH functions and ensure you get the desired results.

Excel Index Match Tutorial
Excel Index Match Tutorial

Before diving into the solutions, let's first understand why blank cells pose a challenge. When a cell is blank, the INDEX MATCH function doesn't have any value to match, leading to an error or a blank result. To overcome this, we'll use IFERROR and IFNA functions to return a specific value or message when the cell is blank.

How to remove blank cells in Excel
How to remove blank cells in Excel

Using IFERROR with INDEX MATCH

The IFERROR function allows you to create a custom error message or return a specific value when an error occurs. By combining IFERROR with INDEX MATCH, you can handle blank cells gracefully.

index match match excel
index match match excel

Here's the syntax to use IFERROR with INDEX MATCH if the cell is blank, returning a specific value (e.g., "No match found"): `=IFERROR(INDEX(MATCH(range, lookup_value)), "No match found")`

Step-by-step process

How to remove duplicates and replace with blank cells in Excel?
How to remove duplicates and replace with blank cells in Excel?

1. Identify the range where you want to search for a match.

2. Specify the lookup value you want to find within the range.

3. Use the INDEX MATCH function to retrieve the corresponding value from the range.

How to Use INDEX MATCH Instead of VLOOKUP in Excel - ExcelDemy
How to Use INDEX MATCH Instead of VLOOKUP in Excel - ExcelDemy

4. Wrap the INDEX MATCH function with IFERROR to handle blank cells and return a custom message.

Example

Suppose you have a table with product names in column A (A2:A10) and their respective prices in column B (B2:B10). You want to find the price of a product named "Apple," but the cell containing the product name is blank. Using the formula `=IFERROR(INDEX(B2:B10, MATCH("Apple", A2:A10, 0)), "No match found")` will return "No match found" if the cell is blank.

a screenshot of a computer screen with the highlighted numbers and data fields in place
a screenshot of a computer screen with the highlighted numbers and data fields in place

Using IFNA with INDEX MATCH

IFNA is an alternative function to IFERROR, specifically designed to handle #N/A errors. Although INDEX MATCH doesn't typically return #N/A errors, using IFNA can provide a more explicit solution when dealing with blank cells.

an invoice form with the words index on it
an invoice form with the words index on it
Excel: If cell contains then count, sum, highlight, copy or delete
Excel: If cell contains then count, sum, highlight, copy or delete
Fill Blank Cells With Value Above or Below the Cell or Zero
Fill Blank Cells With Value Above or Below the Cell or Zero
Fill in blanks in Excel with value above/below, fill empty cells with 0
Fill in blanks in Excel with value above/below, fill empty cells with 0
Remove Blank Cells in Excel
Remove Blank Cells in Excel
How to find address of cell with max or min value in Excel?
How to find address of cell with max or min value in Excel?
How to Delete Blank Cells in a Microsoft Excel
How to Delete Blank Cells in a Microsoft Excel
Delete Blank Rows in Excel, Remove Blank Cells in Excel
Delete Blank Rows in Excel, Remove Blank Cells in Excel
How to Delete Blank Cells in Excel / Google Sheets
How to Delete Blank Cells in Excel / Google Sheets
How to Highlight Blank Cells in Excel (In 4 Easy Ways)
How to Highlight Blank Cells in Excel (In 4 Easy Ways)
printables index
printables index
How to Use the INDEX and MATCH Function in Excel
How to Use the INDEX and MATCH Function in Excel
Count Cells that are Not Blank in Excel
Count Cells that are Not Blank in Excel
How to hide the True/False words within cell when linked to checkbox in Excel?
How to hide the True/False words within cell when linked to checkbox in Excel?
Excel IF Function: If Cell Contains Specific Text - Partial Match IF Formula
Excel IF Function: If Cell Contains Specific Text - Partial Match IF Formula
Fill Blank Cells From Above in Excel
Fill Blank Cells From Above in Excel
Quickly Fill in Blank Cells in an Excel Report
Quickly Fill in Blank Cells in an Excel Report
Blank Flashcards Templates
Blank Flashcards Templates
a blank sign up sheet with lines
a blank sign up sheet with lines
How To Quickly Fill All Blank Cells with A Value in Excel
How To Quickly Fill All Blank Cells with A Value in Excel

The syntax to use IFNA with INDEX MATCH if the cell is blank, returning a specific value (e.g., "No match found"): `=IFNA(INDEX(MATCH(range, lookup_value)), "No match found")`

Step-by-step process

1. Identify the range where you want to search for a match.

2. Specify the lookup value you want to find within the range.

3. Use the INDEX MATCH function to retrieve the corresponding value from the range.

4. Wrap the INDEX MATCH function with IFNA to handle blank cells and return a custom message.

Example

Using the same table as the previous example, the formula `=IFNA(INDEX(B2:B10, MATCH("Apple", A2:A10, 0)), "No match found")` will return "No match found" if the cell containing the product name is blank.

In both cases, using IFERROR or IFNA with INDEX MATCH ensures that blank cells don't cause errors or return unexpected results. Instead, you can provide a clear and informative message, making your spreadsheets more user-friendly and reliable. By mastering these techniques, you'll be well-equipped to handle blank cells and enhance your data analysis skills.