Hi
I am doing a VLOOKUP for a name in a list, and then taking the score
next to the name if the name appears. If the name doesn't appear I
get an #N/A which is fine. However, in my results sheet I am looking
for the name on a number of sheets and tables, to get a list. i.e


100m Swimming Diving
James 75 55 #N/A

I need to do a sum of the score but because of the #N/A I cannot. I
dont want to add the individual cells as eventually a Diving score may
come in. How can I get #N/A values to default to 0 if the name doesnt
exist in the list please?

Cheers

Rich

Hi Rich,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Richhall" wrote in message ups.com...
| Hi
| I am doing a VLOOKUP for a name in a list, and then taking the score
| next to the name if the name appears. If the name doesn't appear I
| get an #N/A which is fine. However, in my results sheet I am looking
| for the name on a number of sheets and tables, to get a list. i.e
|
|
| 100m Swimming Diving
| James 75 55 #N/A
|
| I need to do a sum of the score but because of the #N/A I cannot. I
| dont want to add the individual cells as eventually a Diving score may
| come in. How can I get #N/A values to default to 0 if the name doesnt
| exist in the list please?
|
| Cheers
|
| Rich
|

One way is to use ISNA to return zero, eg
=IF(ISNA(VLOOKUP(E3,A1:B100,2,FALSE)),0,VLOOKUP(E3 ,A1:B100,2,FALSE))
Regards,
Alan.

"Richhall" wrote in message
ups.com...
Hi
I am doing a VLOOKUP for a name in a list, and then taking the score
next to the name if the name appears. If the name doesn't appear I
get an #N/A which is fine. However, in my results sheet I am looking
for the name on a number of sheets and tables, to get a list. i.e


100m Swimming Diving
James 75 55 #N/A

I need to do a sum of the score but because of the #N/A I cannot. I
dont want to add the individual cells as eventually a Diving score may
come in. How can I get #N/A values to default to 0 if the name doesnt
exist in the list please?

Cheers

Rich

Excellent, thank you.