|
| 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. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
|
|
#1
July 8th, 2008, 07:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
July 8th, 2008, 07:37 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
July 8th, 2008, 07:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
July 8th, 2008, 08:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
July 8th, 2008, 08:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
July 8th, 2008, 08:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
July 8th, 2008, 08:49 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
April 28th, 2010, 01:04 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)
|
|
#9
April 28th, 2010, 01:09 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)
|
|
#10
April 28th, 2010, 01:21 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | |
|
|
Hybrid Mode
