Show "-" instead of #N/A

[sythong]

Hi I am back again

How to make the following show "-" instead of #N/A when that
cell AF81 does not contain an entry in the named range?



=INDEX(List1,MATCH(AF81,List2,),2)

Thanks for reading this post

 

[Aladin Akyurek]

Also... Does

=ROWS(List1)=ROWS(List2)

yield TRUE or FALSE?

 

[sythong]

Aladin

Really sorry for being a bit muddled. Please explain what I really
need to do with

What do you get with:

=SUMPRODUCT(ISERROR(List1)+0)

and with:

=SUMPRODUCT(ISERROR(List2)+0)


Also... Does

=ROWS(List1)=ROWS(List2)

yield TRUE or FALSE?

There are no sumproducts in my worksheet

 

[Aladin Akyurek]

Aladin

Really sorry for being a bit muddled. Please explain what I really
need to do with

What do you get with:

=SUMPRODUCT(ISERROR(List1)+0)

and with:

=SUMPRODUCT(ISERROR(List2)+0)


Also... Does

=ROWS(List1)=ROWS(List2)

yield TRUE or FALSE?

There are no sumproducts in my worksheet

I'm trying to diagnose your #REF! problem. I'm asking you to run the above formulas whose results we can use in diagnosing the problem you have?

 

[sythong]

Aladin

Sorry for not fully understanding what was required of me.
Will follow and revert soonest.

Thanks

 

[sythong]

I'm trying to diagnose your #REF! problem. I'm asking you to run the above formulas whose results we can use in diagnosing the problem you have?

Sumproduct returns 0 on both instances

Rows yield TRUE

 

[Aladin Akyurek]

Sumproduct returns 0 on both instances

Rows yield TRUE

So, most obvious explanations are ruled out...

What does

=MATCH(AF81,List2,0)

return?

Please use the look up value that ends up in the #REF! value in this test.

 

[sythong]

So, most obvious explanations are ruled out...

What does

=MATCH(AF81,List2,0)

return?

Please use the look up value that ends up in the #REF! value in this test.

2 different results i.e. #N/A or 157.

Aladin I really admire your resilence for coming with so many
variables to test this problem.

 

[Aladin Akyurek]

2 different results i.e. #N/A or 157.

Aladin I really admire your resilence for coming with so many
variables to test this problem.

I'm trying...

What does...

=INDEX(List1,157)

give?

 

[sythong]

Aladin


It shows #REF

 

[Aladin Akyurek]

Aladin


It shows #REF

This would mean that MATCH returns a row from List2, which does not exist in List1. Would you post the definitions of List1 and List2?