Remove #N/A if index(match()) does not return value

[bloodybrit90]

Hi Guys,

When the following formula does not have a value it returns #N/A. How can I get excel not to display the #N/A? Maybe an if statement. I tried if formula is true return formual....but excel wouldnt move onto the false portion when i typed a comma. Any Ideas?

{=INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0))}

 

[crusader]

=IF(ISNA(INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0))),"",INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0)))

 

[Aladin Akyurek]

Hi Guys,

When the following formula does not have a value it returns #N/A. How can I get excel not to display the #N/A? Maybe an if statement. I tried if formula is true return formual....but excel wouldnt move onto the false portion when i typed a comma. Any Ideas?

{=INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0))}

On Excel 2007 or later...

{=IFERROR(INDEX(Inputs!$G$31:$G$39,
   MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&
    "Bedroom"&" "&Inputs!$B$31:$B$39,0)),"")}

Otherwise...

If a text value is the expected outcome...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",
  INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,
   Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0))))

 

[bloodybrit90]

Worked perfect. Thanks for the help!

 

[Aladin Akyurek]

Worked perfect. Thanks for the help!

You are welcome. Thanks for providing feedback.

 

[T. Valko]

=IF(ISNA(INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0))),"",INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0)))

Since the error will be generated by the MATCH function there is no need to include the INDEX function in the error trap (assuming there are not already errors in any of the referenced ranges). We can just test the MATCH function for the #N/A error.

=IF(ISNA(MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0)),"",INDEX(Inputs!$G$31:$G$39,MATCH(Summary!D39,Inputs!$C$31:$C$39&" "&"Bedroom"&" "&Inputs!$B$31:$B$39,0)))

 

[Liam2003]

Hi there, I am a newbie at these formulas. can you please let me know how I can include the iferror in this to remove #N/A. I would be extremely grateful, I have read and tried a few of these posts, however nothing is working for me. My formula is;

{=INDEX(lastsaledata,MATCH(1,(lastsalecust=$D$5)*(lastsaleprod=$C11),0),3)}

Thank you

 

[Aladin Akyurek]

Hi there, I am a newbie at these formulas. can you please let me know how I can include the iferror in this to remove #N/A. I would be extremely grateful, I have read and tried a few of these posts, however nothing is working for me. My formula is;

{=INDEX(lastsaledata,MATCH(1,(lastsalecust=$D$5)*(lastsaleprod=$C11),0),3)}

Thank you

Try:

{=IFERROR(INDEX(lastsaledata,MATCH(1,IF(lastsalecust=$D$5,IF(lastsaleprod=$C11,1)),0),3),"")}

 

[Liam2003]

Try:

{=IFERROR(INDEX(lastsaledata,MATCH(1,IF(lastsalecust=$D$5,IF(lastsaleprod=$C11,1)),0),3),"")}

Aladin Akyurek, You are a legend. Thank you so very much, Worked Perfectly. ​

 

[T. Valko]

can you please let me know how I can include the iferror in this to remove #N/A.

{=INDEX(lastsaledata,MATCH(1,(lastsalecust=$D$5)*(lastsaleprod=$C11),0),3)}

Like this...

=IFERROR(INDEX(lastsaledata,MATCH(1,(lastsalecust=$D$5)*(lastsaleprod=$C11),0),3),"")

Still array entered.