not found problem

[Kat]

Hi all

The sum below does a search for a number, finds it and all works well, if
the number is not there it reads N/A and causes problems is there any way i
can get this cell to read 0


=INDEX('E Mails'!$I$1:$I$100,MATCH(D3,'E Mails'!$C$1:$C$100,))

thanks

in advance

 

[Frank Kabel]

Hi
one way:
=IF(ISNA(MATCH(D3,'E Mails'!$C$1:$C$100,),0,INDEX('E
Mails'!$I$1:$I$100,MATCH(D3,'E Mails'!$C$1:$C$100,)))

Note: not sure if the following won't be better (if your numbers are
not sorted):
=IF(ISNA(MATCH(D3,'E Mails'!$C$1:$C$100,0),0,INDEX('E
Mails'!$I$1:$I$100,MATCH(D3,'E Mails'!$C$1:$C$100,0)))

 

[Kat]

Thanks for trying although this did not work it shows error on 0 marked in
red


=IF(ISNA(MATCH(D3,'E Mails'!$C$1:$C$100,0),0,INDEX('E
Mails'!$I$1:$I$100,MATCH(D3,'E Mails'!$C$1:$C$100,0)))

 

[Kat]

Hi all

The sum below does a search for a number, finds it and all works well, if
the number is not there it reads N/A and causes problems is there any way i
can get this cell to read 0


=INDEX('E Mails'!$I$1:$I$100,MATCH(D3,'E Mails'!$C$1:$C$100,))

thanks

in advance

 

[Frank Kabel]

Hi Kat
sorry, forgiot one bracket. try:
=IF(ISNA(MATCH(D3,'E Mails'!$C$1:$C$100,0)),0,INDEX('E
Mails'!$I$1:$I$100,MATCH(D3,'E Mails'!$C$1:$C$100,0)))