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 compare or match data from different cells. The INDEX MATCH function in Excel is a powerful tool for this, but what happens when you want to return a blank cell if there's no match? In this guide, we'll explore how to use INDEX MATCH with an IF function to achieve this.

Printable Index Page for Practical Notebook | A4 PDF Template for College Students
Printable Index Page for Practical Notebook | A4 PDF Template for College Students

Before we dive into the solution, let's understand why you might want to return a blank cell. In many cases, you might have a dataset with missing values, and you don't want these to be represented by an error value or a zero. Instead, you want to keep the cell blank, making your data cleaner and easier to read.

Google Image Result for https://m.media-amazon.com/images/I/71bIm6MB9NL._AC_UF350,350_QL80_.jpg
Google Image Result for https://m.media-amazon.com/images/I/71bIm6MB9NL._AC_UF350,350_QL80_.jpg

Understanding INDEX MATCH and IF functions

The INDEX MATCH function in Excel is used to find a specific item in a range of cells, based on the criteria you provide. The IF function, on the other hand, allows you to test a condition and return one value if the condition is true, and another value if it's false.

an invoice form with the words index on it
an invoice form with the words index on it

By combining these two functions, we can create a formula that first tries to find a match using INDEX MATCH, and if no match is found, it returns a blank cell using the IF function.

Setting up your data

Printable Blank Index Cards {3x5, 4x6, 5x7, 5x8} Templates With PDF - Printables Hub
Printable Blank Index Cards {3x5, 4x6, 5x7, 5x8} Templates With PDF - Printables Hub

Let's assume you have the following data in columns A and B:

Column AColumn B
Apple5
Banana3
Cherry7
Date
Elderberry9

In this example, we want to find the value in column B that corresponds to each item in column A. However, we want to return a blank cell if there's no match.

a blank label that is in the shape of a rectangle with two lines on each side
a blank label that is in the shape of a rectangle with two lines on each side

Using INDEX MATCH with IF

The formula to achieve this is as follows:

=IFERROR(INDEX(B:B, MATCH(A2, A:A, 0)), "")

four different blanks are shown in this diagram
four different blanks are shown in this diagram

Let's break down this formula:

  1. MATCH(A2, A:A, 0) - This part of the formula finds the position of the value in cell A2 within the range A:A. The '0' at the end makes the match exact.
  2. INDEX(B:B, [MATCH result]) - This part of the formula uses the position found by the MATCH function to retrieve the corresponding value from column B.
  3. IFERROR([INDEX MATCH result], "") - This part of the formula checks if the INDEX MATCH function returned an error (which it would if there's no match). If it did, it returns a blank cell ("").
printables index
printables index
a blank paper with three horizontal lines on the bottom and one line at the top
a blank paper with three horizontal lines on the bottom and one line at the top
Blank Flashcards Templates
Blank Flashcards Templates
blank sheet.pdf | Powered by Box
blank sheet.pdf | Powered by Box
the index cards are lined up and ready to be printed
the index cards are lined up and ready to be printed
Blank on Blank Animated Interviews
Blank on Blank Animated Interviews
Printable Index Card Templates: 3x5 and 4x6 Blank PDFs
Printable Index Card Templates: 3x5 and 4x6 Blank PDFs
Printable Index Page Designs [PDF Included] - Printables Hub
Printable Index Page Designs [PDF Included] - Printables Hub
four squares are shown in the middle of each square, and one is blank for text
four squares are shown in the middle of each square, and one is blank for text
an image of a family tree with different colors and shapes on it's sides
an image of a family tree with different colors and shapes on it's sides
blank labels are shown in the shape of rectangles
blank labels are shown in the shape of rectangles
schedule
schedule
a blank graph sheet with the words for printable blank numbers and place names on it
a blank graph sheet with the words for printable blank numbers and place names on it
a blank diagram with four square sections
a blank diagram with four square sections
a blank sheet with lines on it
a blank sheet with lines on it
Blank Playing Card Template For Word - 10 Free PDF Printables | Printablee
Blank Playing Card Template For Word - 10 Free PDF Printables | Printablee
a black and white id card with a woman's name in the center on it
a black and white id card with a woman's name in the center on it

When you enter this formula in cell C2 and drag it down, you'll see that it returns the corresponding values from column B, and blank cells where there's no match.

Handling multiple criteria

What if you want to find a match based on multiple criteria? For example, you might want to find the value in column B that corresponds to an item in column A, but only if that item is also in column C.

The formula to achieve this is a bit more complex:

=IFERROR(INDEX(B:B, MATCH(1, (A:A=C2)*(A:A=D2), 0)), "")

In this formula, we're using the (A:A=C2)*(A:A=D2) part to create an array of TRUE/FALSE values, where TRUE indicates a match on both criteria. The MATCH function then finds the position of the first TRUE value in this array.

Using structured references

If you're using Excel Tables, you can make your formulas more readable and easier to maintain by using structured references. The formula above would look like this:

=IFERROR(INDEX(Table1[B], MATCH(1, (Table1[A]=C2)*(Table1[A]=D2), 0)), "")

In this formula, Table1 is the name of your table, and [A] and [B] are the names of the columns within that table.

And there you have it! With these formulas, you can use INDEX MATCH to find specific values in your spreadsheets, and return a blank cell if there's no match. Happy calculating!