A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how do i replace #n/a in a vlookup?



 
 
Thread Tools Display Modes
  #11  
Old July 9th, 2008, 02:49 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default how do i replace #n/a in a vlookup?

You're welcome, Steve.

Try to work out what the formula is actually doing - IF(ISNA(...) means "If
it is an error", so basically the formula says:

If it is an error then return zero (was blank), otherwise return the result
of the VLOOKUP.

Pete

"Steve" wrote in message
...
Perfect thank you

"Pete_UK" wrote:

Change the "" to a zero in the middle of the formula.

Hope this helps.

Pete

"Steve" wrote in message
...
Hello,

i've done this and now i get a blank instead of #n/a, here is mine:

=IF(ISNA(VLOOKUP(F2,BUYS!$F:$P,7,FALSE)),"",VLOOKU P(F2,BUYS!$F:$P,7,FALSE))

Any thoughts on how to get it to be a zero?

"Mike H" wrote:

Try

=IF(ISNA(VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE)),"",VLOOKUP(L2,'Ma rch
Chargebacks'!A$1:F$613,6,FALSE))

Mike

"infinite1013" wrote:

Can you please show me how to replace the #N/A result with 0?
=VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE)






  #12  
Old October 23rd, 2009, 05:12 PM posted to microsoft.public.excel.worksheet.functions
Richard
external usenet poster
 
Posts: 1,419
Default how do i replace #n/a in a vlookup?

Hi,

I'm trying to apply the same logic but with a different formula. In CELL
AC3 I either get a number value or a #N/A. How can I apply your formula/logic
so my formula says FALSE when a #N/A value appears in cell AC3?


=IF(ABS(AC3)=5,"TRUE","FALSE")



"Pete_UK" wrote:

You're welcome, Steve.

Try to work out what the formula is actually doing - IF(ISNA(...) means "If
it is an error", so basically the formula says:

If it is an error then return zero (was blank), otherwise return the result
of the VLOOKUP.

Pete

"Steve" wrote in message
...
Perfect thank you

"Pete_UK" wrote:

Change the "" to a zero in the middle of the formula.

Hope this helps.

Pete

"Steve" wrote in message
...
Hello,

i've done this and now i get a blank instead of #n/a, here is mine:

=IF(ISNA(VLOOKUP(F2,BUYS!$F:$P,7,FALSE)),"",VLOOKU P(F2,BUYS!$F:$P,7,FALSE))

Any thoughts on how to get it to be a zero?

"Mike H" wrote:

Try

=IF(ISNA(VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE)),"",VLOOKUP(L2,'Ma rch
Chargebacks'!A$1:F$613,6,FALSE))

Mike

"infinite1013" wrote:

Can you please show me how to replace the #N/A result with 0?
=VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE)






  #13  
Old October 23rd, 2009, 05:23 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default how do i replace #n/a in a vlookup?

=if(isna(ac3),false,if(abs(ac3)5,true,false))
or
=if(isna(ac3),false,abs(ac3)5)

These will return the booleans TRUE and FALSE--not strings.

I think I'd check for a number:
=if(not(isnumber(ac3)),false,abs(ac3)5)

Richard wrote:

Hi,

I'm trying to apply the same logic but with a different formula. In CELL
AC3 I either get a number value or a #N/A. How can I apply your formula/logic
so my formula says FALSE when a #N/A value appears in cell AC3?

=IF(ABS(AC3)=5,"TRUE","FALSE")

"Pete_UK" wrote:

You're welcome, Steve.

Try to work out what the formula is actually doing - IF(ISNA(...) means "If
it is an error", so basically the formula says:

If it is an error then return zero (was blank), otherwise return the result
of the VLOOKUP.

Pete

"Steve" wrote in message
...
Perfect thank you

"Pete_UK" wrote:

Change the "" to a zero in the middle of the formula.

Hope this helps.

Pete

"Steve" wrote in message
...
Hello,

i've done this and now i get a blank instead of #n/a, here is mine:

=IF(ISNA(VLOOKUP(F2,BUYS!$F:$P,7,FALSE)),"",VLOOKU P(F2,BUYS!$F:$P,7,FALSE))

Any thoughts on how to get it to be a zero?

"Mike H" wrote:

Try

=IF(ISNA(VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE)),"",VLOOKUP(L2,'Ma rch
Chargebacks'!A$1:F$613,6,FALSE))

Mike

"infinite1013" wrote:

Can you please show me how to replace the #N/A result with 0?
=VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE)







--

Dave Peterson
  #14  
Old April 28th, 2010, 01:04 AM posted to microsoft.public.excel.worksheet.functions
Casper
external usenet poster
 
Posts: 13
Default how do i replace #n/a in a vlookup?

Hi Mike or anybody else
I want to use the same formula for my spreadsheet but have to return a date
and if there is no date it just have to be blank what do I have to replace in
the formula to get it right because now my dates are all wrong
Regards
Casper

"Mike H" wrote:

Hi,

If there is no value in L2 or the formula cannot match that value then it
will produce the #NA error and the modification I gave you should cure that.

If there is a value in L2 and it finds a match on the worksheet 'March
Chargebacks' and there is no value in column F then that's when it returns 0
(zero).

I don't understand what the question now is.

Mike


"infinite1013" wrote:

Thanks, but when I entered this, I get 0 for every answer that it is copied
to. Is there any other way to set this up? The original formula is designed
to use the number in L2 to find its match on another worksheet and return a
percentage that is in column six of that page. It returns the correct
percentage, when there is one. I want to clean up the worksheet by getting
rid of the #N/A. Thanks again.

"Mike H" wrote:

Try

=IF(ISNA(VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE)),"",VLOOKUP(L2,'Ma rch
Chargebacks'!A$1:F$613,6,FALSE))

Mike

"infinite1013" wrote:

Can you please show me how to replace the #N/A result with 0?
=VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE)

  #15  
Old April 28th, 2010, 01:09 AM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,386
Default how do i replace #n/a in a vlookup?

The standard way is:
=if(iserror(yourformula),"",yourformula)

If you have Excel 2007, you can use:
=iferror(yourformula,"")

Regards,
Fred

"Casper" wrote in message
...
Hi Mike or anybody else
I want to use the same formula for my spreadsheet but have to return a
date
and if there is no date it just have to be blank what do I have to replace
in
the formula to get it right because now my dates are all wrong
Regards
Casper

"Mike H" wrote:

Hi,

If there is no value in L2 or the formula cannot match that value then it
will produce the #NA error and the modification I gave you should cure
that.

If there is a value in L2 and it finds a match on the worksheet 'March
Chargebacks' and there is no value in column F then that's when it
returns 0
(zero).

I don't understand what the question now is.

Mike


"infinite1013" wrote:

Thanks, but when I entered this, I get 0 for every answer that it is
copied
to. Is there any other way to set this up? The original formula is
designed
to use the number in L2 to find its match on another worksheet and
return a
percentage that is in column six of that page. It returns the correct
percentage, when there is one. I want to clean up the worksheet by
getting
rid of the #N/A. Thanks again.

"Mike H" wrote:

Try

=IF(ISNA(VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE)),"",VLOOKUP(L2,'Ma rch
Chargebacks'!A$1:F$613,6,FALSE))

Mike

"infinite1013" wrote:

Can you please show me how to replace the #N/A result with 0?
=VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE)


  #16  
Old April 28th, 2010, 01:21 AM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default how do i replace #n/a in a vlookup?

Mike's ISNA formula will return a blank cell if no data is found

But why do you say your dates are all wrong?

What has that got to do getting it right in the formula?

The formula won't make your dates wrong.


Gord Dibben MS Excel MVP


On Tue, 27 Apr 2010 17:04:01 -0700, Casper
wrote:

Hi Mike or anybody else
I want to use the same formula for my spreadsheet but have to return a date
and if there is no date it just have to be blank what do I have to replace in
the formula to get it right because now my dates are all wrong
Regards
Casper

"Mike H" wrote:

Hi,

If there is no value in L2 or the formula cannot match that value then it
will produce the #NA error and the modification I gave you should cure that.

If there is a value in L2 and it finds a match on the worksheet 'March
Chargebacks' and there is no value in column F then that's when it returns 0
(zero).

I don't understand what the question now is.

Mike


"infinite1013" wrote:

Thanks, but when I entered this, I get 0 for every answer that it is copied
to. Is there any other way to set this up? The original formula is designed
to use the number in L2 to find its match on another worksheet and return a
percentage that is in column six of that page. It returns the correct
percentage, when there is one. I want to clean up the worksheet by getting
rid of the #N/A. Thanks again.

"Mike H" wrote:

Try

=IF(ISNA(VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE)),"",VLOOKUP(L2,'Ma rch
Chargebacks'!A$1:F$613,6,FALSE))

Mike

"infinite1013" wrote:

Can you please show me how to replace the #N/A result with 0?
=VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE)


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.