Why isn't that INDEX and MATCH returing #NA

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
698
Office Version
  1. 2010
Platform
  1. Windows
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))

Could you help please.


Regards,

Sean
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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))

Could you help please.


Regards,

Sean

Hi Sean,

This link is very helpful with Index/Match and Index/Match/Match problems: https://www.deskbright.com/excel/using-index-match/
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.
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
@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
 
Upvote 0
Like this:


=IFERROR(INDEX(Sheet1!$A$2:$A$2201,MATCH(E19,Sheet1!$B$2:$B$2201,0),MATCH(AY19,Sheet1!$B$2:$B$2201,0)),"Not Found")
 
Upvote 0
@AliGW:

post cancelled by Sean.
 
Last edited:
Upvote 0
@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.
 
Upvote 0
@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.

Revised formula =IFERROR(INDEX(sheet1!$A$2:$A$2201,MATCH(E5,sheet1!$B$2:$B$2201,0),MATCH(AY5,sheet1!$B$2:$B$2201,0)),"Not Found")


Regards,

Sean
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top