Why isn't that INDEX and MATCH returing #NA

[Sean15]

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

 

[MattH1]

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.

 

[Sean15]

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]

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

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

 

[Sean15]

ADVERTISEMENT

@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

 

[AliGW]

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")

 

[Sean15]

ADVERTISEMENT

@AliGW:

post cancelled by Sean.

 

[AliGW]

 

[AhoyNC]

@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]

@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