Error handling VLOOKUP. #N/A - can it be 0 to allow sumation.

[Richhall]

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

 

[Niek Otten]

Hi Rich,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
|

 

[Alan]

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]

Excellent, thank you.