#### nancybrown

##### Well-known Member
My index statement returns a value of #N/A or 0 for unmatched records. Can something be added to my formula below that will return a "blank" for unmatched items?

INDEX(SHEET2!\$E\$3:\$E\$10,MATCH(SHEET1!\$A\$1:\$A\$30,SHEET2!\$A\$1:\$A:\$30,0))

Many, many thanks!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Diablo II

##### Well-known Member
remove the 0 at the end and put "".

##### MrExcel MVP
My index statement returns a value of #N/A or 0 for unmatched records. Can something be added to my formula below that will return a "blank" for unmatched items?

INDEX(SHEET2!\$E\$3:\$E\$10,MATCH(SHEET1!\$A\$1:\$A\$30,SHEET2!\$A\$1:\$A:\$30,0))

Many, many thanks!

If on Excel 2007 or beyond...
Code:
=IFERROR(INDEX(SHEET2!\$E\$3:\$E\$10,
MATCH(SHEET1!\$A\$1:\$A\$30,SHEET2!\$A\$1:\$A:\$30,0)),"")

Otherwise:
Code:
=IF(ISNUMBER(MATCH(SHEET1!\$A\$1:\$A\$30,SHEET2!\$A\$1:\$A:\$30,0)),
INDEX(SHEET2!\$E\$3:\$E\$10,
MATCH(SHEET1!\$A\$1:\$A\$30,SHEET2!\$A\$1:\$A:\$30,0)),"")

#### VoG

##### Legend
Try

=IF(ISNA(MATCH(Sheet1!\$A\$1:\$A\$30,Sheet2!\$A\$1:\$A\$30,0)),"",INDEX(Sheet2!\$E\$3:\$E\$10,MATCH(Sheet1!\$A\$1:\$A\$30,Sheet2!\$A\$1:\$A\$30,0)))

#### nancybrown

##### Well-known Member
I should have thought of that . . . .a little slow to the gate!!!!

Thanks so much!

Replies
9
Views
275
Replies
2
Views
137
Replies
7
Views
733
Replies
9
Views
239
Replies
0
Views
230

1,196,027
Messages
6,012,950
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back