#### nancybrown

##### Well-known Member
What do I need to do this formula so when I do an Index, no matches return blank? I tried adding ,"")) to the end, but it didn't work.

=index(sheet2!\$A:\$2:\$A\$300,match(sheet1!\$B\$2:\$B\$300,sheet2!\$B\$2:\$B\$300,0,),"")

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

##### MrExcel MVP
What do I need to do this formula so when I do an Index, no matches return blank? I tried adding ,"")) to the end, but it didn't work.

=index(sheet2!\$A:\$2:\$A\$300,match(sheet1!\$B\$2:\$B\$300,sheet2!\$B\$2:\$B\$300,0,),"")

Assuming that the formula is on sheet1 and B2 on sheet1 is the first lookup value...

If you are on Excel 2007 or beyond:

=IFERROR(INDEX(sheet2!\$A\$2:\$A\$300,MATCH(B2,sheet2!\$B\$2:\$B\$300,0)),"")

Otherwise:
Code:
``````=IF(ISNUMBER(MATCH(B2,sheet2!\$B\$2:\$B\$300,0)),
INDEX(sheet2!\$A\$2:\$A\$300,MATCH(B2,sheet2!\$B\$2:\$B\$300,0),
"")``````

Many Thanks!

Replies
5
Views
238
Replies
3
Views
203
Replies
2
Views
360
Replies
0
Views
537
Replies
7
Views
692

1,195,624
Messages
6,010,751
Members
441,568
Latest member
abbyabby

### 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