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

 

[GK039]

Try in 2007:

Iferror(INDEX(List1,MATCH(AF81,List2,),2),"-")

and in 2003
If(iserror(INDEX(List1,MATCH(AF81,List2,),2),"-",INDEX(List1,MATCH(AF81,List2,),2))

George

QUOTE=sythong;2678582]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[/QUOTE]

 

[Aladin Akyurek]

On systems prior to 2007...

=IF(ISNUMBER(MATCH(AF81,List2,0)),INDEX(List1,MATCH(AF81,List2,0)),"-")

would be a bit less costly.

 

[GK039]

Hi Aladin,

Being less costly you mean in terms of memory and time that the formula is being evaluated?

Thanks in advance for your input

George

On systems prior to 2007...

=IF(ISNUMBER(MATCH(AF81,List2,0)),INDEX(List1,MATCH(AF81,List2,0)),"-")

would be a bit less costly.

 

[sythong]

Aladin

Your suggestion worked

George

If(iserror(INDEX(List1,MATCH(AF81,List2,),2),"-",INDEX(List1,MATCH(AF81,List2,),2))
doesnt seems to work for me.

Thanks for the suggestions

 

[Aladin Akyurek]

Hi Aladin,

Being less costly you mean in terms of memory and time that the formula is being evaluated?

Thanks in advance for your input

George

Means: A tad faster or a better temporal profile, if you wish...

 

[sythong]

Aladin



Now I have a problem. It shows #REF when I
put a dash instead of alpha or numeric details.

Any suggestions
Thanks

 

[Aladin Akyurek]

Aladin



Now I have a problem. It shows #REF when I
put a dash instead of alpha or numeric details.

Any suggestions
Thanks

Are you feeding the result of:

=IF(ISNUMBER(MATCH(AF81,List2,0)),INDEX(List1,MATCH(AF81,List2,0)),"-")

to another formula where you get a #REF! error or what? Care to elaborate?

 

[sythong]

Aladin

First thank you very much for responding.

My initial syntax was =INDEX(List1,MATCH(AF81,List2,),2)
This was used to lookup the individual code for debtor and creditor.
It worked fine when the cells are input with the respective debtor
and creditor name but my account trial balance consist not only of
debtors and creditors names but also others. When this is input with
the debtor/creditor it will show correctly the respective code but
other names or dash will show #N/A.

On using =IF(ISNUMBER(MATCH(AF81,List2,0)),INDEX(List1,MATCH(AF81,List2,0)),"-")
it also worked on debtors/creditors perfectly plus certain cells but will also throw
up #REF

This has really got me floored

 

[Aladin Akyurek]

Aladin

First thank you very much for responding.

My initial syntax was =INDEX(List1,MATCH(AF81,List2,),2)
This was used to lookup the individual code for debtor and creditor.
It worked fine when the cells are input with the respective debtor
and creditor name but my account trial balance consist not only of
debtors and creditors names but also others. When this is input with
the debtor/creditor it will show correctly the respective code but
other names or dash will show #N/A.

On using =IF(ISNUMBER(MATCH(AF81,List2,0)),INDEX(List1,MATCH(AF81,List2,0)),"-")
it also worked on debtors/creditors perfectly plus certain cells but will also throw
up #REF

This has really got me floored

What do you get with:

=SUMPRODUCT(ISERROR(List1)+0)

and with:

=SUMPRODUCT(ISERROR(List2)+0)