Hide #N/As with Cell Indexing/Matching
 

Iwas wondering if anyone knows how I can get the "#N/A" to stop being
displayed for line items that are not found? The first formula is the one I
originally used and the second formula was my attempt at having it display a
"No" instead of "#N/A". The second formula fails... It shows the "Yes" Just
Fine but when it comes to the no part is flops and shows #N/A.

INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)

=IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes")


Thanks in Advance,
Rob

Hide #N/As with Cell Indexing/Matching
 

tried:

=IF(ISERROR(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$15 8,0),1)),"No","Yes")

?


On 13 Sty, 12:11, Rob wrote:
Iwas wondering if anyone knows how I can get the "#N/A" to stop being
displayed for line items that are not found? *The first formula is the one I
originally used and the second formula was my attempt at having it display a
"No" instead of "#N/A". *The second formula fails... *It shows the "Yes" Just
Fine but when it comes to the no part is flops and shows #N/A.

INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)

=IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes")

Thanks in Advance,
Rob

Hide #N/As with Cell Indexing/Matching
 

Rob,

Maybe this

=IF(ISNA(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0 ),1)),"No","Yes")

or even this

=IF(ISNA(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0 ),1)),"No",INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158 ,0),1))

Mike

"Rob" wrote:

Iwas wondering if anyone knows how I can get the "#N/A" to stop being
displayed for line items that are not found? The first formula is the one I
originally used and the second formula was my attempt at having it display a
"No" instead of "#N/A". The second formula fails... It shows the "Yes" Just
Fine but when it comes to the no part is flops and shows #N/A.

INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)

=IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes")


Thanks in Advance,
Rob

Hide #N/As with Cell Indexing/Matching
 

You can use ISNA to trap just the #N/A errors:

=IF(ISNA(MATCH($E6,$K$6:$K$158,0)),"no",INDEX($K$6 :$K$158,MATCH($E6,$K
$6:$K$158,0),1))

Hope this helps.

Pete

On Jan 13, 11:11*am, Rob wrote:
Iwas wondering if anyone knows how I can get the "#N/A" to stop being
displayed for line items that are not found? *The first formula is the one I
originally used and the second formula was my attempt at having it display a
"No" instead of "#N/A". *The second formula fails... *It shows the "Yes" Just
Fine but when it comes to the no part is flops and shows #N/A.

INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)

=IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes")

Thanks in Advance,
Rob

Hide #N/As with Cell Indexing/Matching
 

WOW !! That's Awesome.. They all worked! Thanks You Very Much.

Rob


"Pete_UK" wrote:

You can use ISNA to trap just the #N/A errors:

=IF(ISNA(MATCH($E6,$K$6:$K$158,0)),"no",INDEX($K$6 :$K$158,MATCH($E6,$K
$6:$K$158,0),1))

Hope this helps.

Pete

On Jan 13, 11:11 am, Rob wrote:
Iwas wondering if anyone knows how I can get the "#N/A" to stop being
displayed for line items that are not found? The first formula is the one I
originally used and the second formula was my attempt at having it display a
"No" instead of "#N/A". The second formula fails... It shows the "Yes" Just
Fine but when it comes to the no part is flops and shows #N/A.

INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)

=IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes")

Thanks in Advance,
Rob

Hide #N/As with Cell Indexing/Matching
 

You're welcome, Rob - thanks for feeding back.

Pete

On Jan 13, 12:14*pm, Rob wrote:
WOW !! That's Awesome.. They all worked! *Thanks You Very Much. *

Rob



"Pete_UK" wrote:
You can use ISNA to trap just the #N/A errors:

=IF(ISNA(MATCH($E6,$K$6:$K$158,0)),"no",INDEX($K$6 :$K$158,MATCH($E6,$K
$6:$K$158,0),1))

Hope this helps.

Pete

On Jan 13, 11:11 am, Rob wrote:
Iwas wondering if anyone knows how I can get the "#N/A" to stop being
displayed for line items that are not found? *The first formula is the one I
originally used and the second formula was my attempt at having it display a
"No" instead of "#N/A". *The second formula fails... *It shows the "Yes" Just
Fine but when it comes to the no part is flops and shows #N/A.

INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)

=IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes")

Thanks in Advance,
Rob- Hide quoted text -

- Show quoted text -