I have a large workbook that I am using vlookup in. It is returning #n/a wher
e there is no data to pull. I am trying to add another formula to pull a
range of numbers from the main sheet. It works fine as long as there are no
NA's in the data I am pulling from. I need to know how to change the formula
to not return the N/A but not ruin the formula if the data is there to be
pulled.

Thanks in advance for any help

The usual way of doing this is:

=IF(ISNA(your_vlookup),"",your_vlookup)

or you might have a zero instead of "", or some other message like
"not present".

Hope this helps.

Pete


On Jul 4, 11:52 pm, Toolbar Confused
wrote:
I have a large workbook that I am using vlookup in. It is returning #n/a wher
e there is no data to pull. I am trying to add another formula to pull a
range of numbers from the main sheet. It works fine as long as there are no
NA's in the data I am pulling from. I need to know how to change the formula
to not return the N/A but not ruin the formula if the data is there to be
pulled.

Thanks in advance for any help

Sample formula to return blank if no data.

=IF(ISNA(VLOOKUP(cellref,table,col,FALSE)),"",VLOO KUP(cellref,table,col,FALSE))


Gord Dibben MS Excel MVP

On Wed, 4 Jul 2007 15:52:02 -0700, Toolbar Confused
wrote:

I have a large workbook that I am using vlookup in. It is returning #n/a wher
e there is no data to pull. I am trying to add another formula to pull a
range of numbers from the main sheet. It works fine as long as there are no
NA's in the data I am pulling from. I need to know how to change the formula
to not return the N/A but not ruin the formula if the data is there to be
pulled.

Thanks in advance for any help

To return a 0:
=if(isna(vlookup(...)),0,vlookup(....))

To return an empty string:
=if(isna(vlookup(...)),"",vlookup(....))

If you're using xl2007, you could use:
=iferror(vlookup(...),0)
or
=iferror(vlookup(...),"")



Toolbar Confused wrote:

I have a large workbook that I am using vlookup in. It is returning #n/a wher
e there is no data to pull. I am trying to add another formula to pull a
range of numbers from the main sheet. It works fine as long as there are no
NA's in the data I am pulling from. I need to know how to change the formula
to not return the N/A but not ruin the formula if the data is there to be
pulled.

Thanks in advance for any help

--

Dave Peterson

Thank you all very helpful

"Pete_UK" wrote:

The usual way of doing this is:

=IF(ISNA(your_vlookup),"",your_vlookup)

or you might have a zero instead of "", or some other message like
"not present".

Hope this helps.

Pete


On Jul 4, 11:52 pm, Toolbar Confused
wrote:
I have a large workbook that I am using vlookup in. It is returning #n/a wher
e there is no data to pull. I am trying to add another formula to pull a
range of numbers from the main sheet. It works fine as long as there are no
NA's in the data I am pulling from. I need to know how to change the formula
to not return the N/A but not ruin the formula if the data is there to be
pulled.

Thanks in advance for any help