# Why isn't that INDEX and MATCH returing #NA

#### Sean15

##### Well-known Member
Hello:

This formula is return #NA.

Look up value in AY2 and E2 are sheet #2

INDEX(Sheet1!\$A\$2:\$A\$2201,MATCH(AY2,Sheet1!\$B\$2:\$B\$2201,0),MATCH(E2,Sheet1!\$B\$2:\$C\$2201,0))

Regards,

Sean

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### MattH1

##### Board Regular
Hello:

This formula is return #NA.

Look up value in AY2 and E2 are sheet #2

INDEX(Sheet1!\$A\$2:\$A\$2201,MATCH(AY2,Sheet1!\$B\$2:\$B\$2201,0),MATCH(E2,Sheet1!\$B\$2:\$C\$2201,0))

Regards,

Sean

Hi Sean,

https://www.deskbright.com/excel/using-index-match-match/

If you would like me to personally help you, I'm going to need a bit more information. For example, Index/Match typically only contain one Match if you're going from A:A (or Sheet1!\$A\$2:\$A\$2201 in your case.) If you would like to use Index/Match/Match, I would suggest expanding the LOOKUP_RANGE on your Index function so that it can move over the number of rows your second match outputs.

=INDEX(Sheet1!\$A\$2:\$A\$2201,MATCH(AY2,Sheet1!\$B\$2:\$B\$2201,0)) is an example of an Index/Match function.
=INDEX(Sheet1!\$A\$2:\$D\$2201,MATCH(AY2,Sheet1!\$B\$2:\$B\$2201,0),MATCH(E2,Sheet1!\$B\$2:\$C\$2201,0)) is an example of an Index/Match/Match function.

#### Sean15

##### Well-known Member
Hello:

Index range is in sheet 1 column A (these are the values I want to return)

Lookupvalue1 is in cell AY2 located in sheet named Master

Lookupvalue 2 is in cell E2 located in sheet named Master.

Look range is in sheet 1 column B

So I want to look up values in sheet Master, but look up range is the sheet1 and so is the index range.

Is this possible in Excel?

Sean

#### AhoyNC

##### Well-known Member
Maybe something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Code:
``=INDEX(\$A\$2:\$A\$2201,MATCH(1,IF(Sheet1!B2:B2201=Master!AY2,IF(Sheet1!C2:C2201=Master!E2,1)),0))``

#### Sean15

##### Well-known Member
@AhoyNC.

Thank you very much.

I re-worked formula in post #2 and ended up with:

INDEX(Sheet1!\$A\$2:\$A\$2201,IFERROR(MATCH(E19,Sheet1!\$B\$2:\$B\$2201,0),MATCH(AY19,Sheet1!\$B\$2:\$B\$2201,0)))

So formula is returning #NA for numbers it can't match. How can I edit formula to return "Not found" instead of #NA.

Regards,

Sean

Like this:

#### Sean15

##### Well-known Member
@AliGW:

post cancelled by Sean.

Last edited:

#### AhoyNC

##### Well-known Member
@Sean15
The first match in the INDEX returns a row # the second MATCH returns the column # for INDEX.
MATCH(AY19,Sheet1!\$B\$2:\$B\$2201,0) is this really a column # you want for INDEX?
If you still need help it would help if you could post a small sample of your data and what you expect for an answer.

#### Sean15

##### Well-known Member
@AhoyNC

Formula =INDEX(sheet1!\$A\$2:\$A\$2201,IFERROR(MATCH(E8,sheet1!\$B\$2:\$B\$2201,0),MATCH(AY8,sheet1!\$B\$2:\$B\$2201,0)))

is returning expected results. But formula returned 200+ #NA for numbers it cannot match. Can formula be made to return "Not Found" instead of #NA?

I used AliQW's suggestion in post #6 but revised formula is now returning "not found" for numbers it can and cannot match.

Regards,

Sean

Replies
3
Views
137
Replies
1
Views
271
Replies
0
Views
540
Replies
6
Views
358
Replies
3
Views
390

1,195,689
Messages
6,011,161
Members
441,590
Latest member
kukaljcanin

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