How to stop "#N/A" from displaying in Excel

[joem28a]

Hello,
I have been using the INDEX/MATCH function successfully in Excel. Unfortunately, when a cell has no value, it presents a "#N/A"; which doesn't look too good. I am using the following syntax:

=INDEX(Sheet2!A2:E453,MATCH(A6,Sheet2!A2:A453,0),2)

Can someone plase advise how to remove or stop the "#N/A" from displaying?. Many thanks.

Regards,
Joe

 

[crobg]

You can add an If statement that checks the error.type:
=IF(ERROR.TYPE(INDEX(Sheet2!A2:E453,MATCH(A6,Sheet2!A2:A453,0),2))=7,"",INDEX(Sheet2!A2:E453,MATCH(A6,Sheet2!A2:A453,0),2))

 

[joem28a]

Hello,

Thank you for your prompt reply. Almost there ..... your code worked great, in that it no longer displayed the "#N/A"; however, when selecting a valid entry it display "#N/A" every other time instead of the matched value in Sheet 2. Many thanks.

Regards,
Joe

 

[TonyJollans]

Hi joem28a,

You can only use [blue]ERROR.TYPE[/blue] with an error. When your lookup succeeds you have no error and the ERROR.TYPE returns #N/A.

To check explicitly for #N/A, use the ISNA Function instead - or, more generally, use the ISERROR Function ..

[blue][tt] =IF(ISNA(INDEX(Sheet2!A2:E453,MATCH(A6,Sheet2!A2:A453,0),2)),"", INDEX(Sheet2!A2:E453,MATCH(A6,Sheet2!A2:A453,0),2))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[GlennUK]

Hi Tony,

you don't need to check the whole INDEX formula, just the MATCH part, like this:

=IF(ISNA(MATCH(A6,Sheet2!A2:A453,0)),"", INDEX(Sheet2!A2:E453,MATCH(A6,Sheet2!A2:A453,0),2))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

Well spotted Glenn
I hadn't even looked at the formula.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[joem28a]

Hi Glenn & Tony,

Well done; thank you very much for that. All worked beautifully !!!. Greatly appreciated; take care.

Regards,

Joe.