Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 8th 08, 07:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 13
Default how do i replace #n/a in a vlookup?

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

  #2   Report Post  
Old July 8th 08, 07:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default how do i replace #n/a in a vlookup?

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)

  #3   Report Post  
Old July 8th 08, 07:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 13
Default how do i replace #n/a in a vlookup?

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)

  #4   Report Post  
Old July 8th 08, 08:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default how do i replace #n/a in a vlookup?

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)

  #5   Report Post  
Old July 8th 08, 08:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 13
Default how do i replace #n/a in a vlookup?

Thanks Mike. I was missing a parenthesis when transferring your formula to my
spreadsheet. It works perfect now! Just what i needed. Thanks so much!

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



  #6   Report Post  
Old July 8th 08, 08:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default how do i replace #n/a in a vlookup?

Glad I could help

"infinite1013" wrote:

Thanks Mike. I was missing a parenthesis when transferring your formula to my
spreadsheet. It works perfect now! Just what i needed. Thanks so much!

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

  #7   Report Post  
Old July 8th 08, 08:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 1
Default how do i replace #n/a in a vlookup?

I was missing a parenthesis when transferring your formula
to my spreadsheet.


Copy/Paste'ing into the Formula Bar (followed by removing any line feeds
produced by your newsreader) usually avoids that kind of problem.

Rick

  #8   Report Post  
Old July 9th 08, 02:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,814
Default how do i replace #n/a in a vlookup?

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)

  #9   Report Post  
Old July 9th 08, 02:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default how do i replace #n/a in a vlookup?

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)



  #10   Report Post  
Old July 9th 08, 02:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,814
Default how do i replace #n/a in a vlookup?

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)






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace VLOOKUP results with data Morto Kopor Excel Worksheet Functions 1 December 31st 07 07:25 AM
Replace a vlookup #N/A result with a value Brandon Excel Worksheet Functions 2 August 9th 07 05:40 PM
Replace Vlookup with a macro Arain Excel Discussion (Misc queries) 1 September 27th 06 09:32 AM
How do I replace a return value of #N/A in a vlookup with zero? marketingman Excel Worksheet Functions 4 October 28th 05 09:45 PM
When VLOOKUP returns a #N/A How can you get it to replace #N/A wi. Cmatise Excel Worksheet Functions 10 January 12th 05 12:29 AM


All times are GMT +1. The time now is 06:15 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017