Vlookup returning #NA

[danwaddy]

Hi all, i have a formula =VLOOKUP("x",E10:F13,2) which is returning #NA and i want it to return 0 (Zero)

How can i achieve this?

hope you can help.

 

[dispelthemyth]

You mean the right answer is Zero or if it returns an error (#NA) then you want it to be a zero?

Just to confirm if the correct answer should be Zero, does column E contain an x in rows 10 - 13?
then does Column F contain a Zero on the same row as the x?

 

[danwaddy]

Hi

if it returns an error (#NA) then i want it to be a zero.

column E doesnt contain an x in rows 10 - 13 at the moment but will when populated. Column F defines the value to be shown in the cell with the formula

Thanks for the response

 

[dispelthemyth]

Are you trying to do an exact match and is your search range going to be the same in each of the formuals you use?

if you want an exact match, you current formula becomes the below (added a ,0 at the end)
=VLOOKUP("x",E10:F13,2, 0)

if you want the range of cells to be the same if you copy this formula down it becomes the below (added $ signs to stop the range changing as you copy the formula

=VLOOKUP("x",$E$10:$F$13,2,0)

Now to your errors, Depending on your version of Excel try the below

=iferror(VLOOKUP("x",$E$10:$F$13,2, 0),0)



or

​=IF(ISERROR(VLOOKUP("x",$E$10:$F$13,2,0)),0,VLOOKUP("x",$E$10:$F$13,2,0))

 

[danwaddy]

This works perfect =iferror(VLOOKUP("x",$E$10:$F$13,2, 0),0)

thanks for this, much appreciated.