|
| 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
July 9th, 2008, 02:06 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
July 9th, 2008, 02:18 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
July 9th, 2008, 02:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
