| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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
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) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
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) |
|
#15
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) |
|
#16
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) |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i replace #n/a in a vlookup?
Am Dienstag, 8. Juli 2008 20:28:21 UTC+2 schrieb infinite1013:
Can you please show me how to replace the #N/A result with 0? =VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE) =IFERROR(VLOOKUP(...), 0) |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i replace #n/a in a vlookup?
On Wednesday, July 9, 2008 3:58:21 AM UTC+9:30, 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) I found it easier to add an extra line into my data table, put 0 into the lookup column then I left the correspondinding cell blank, this removed the N/A and left a blank cell, if you want 0 put 0 in the blank cell. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace VLOOKUP results with data | Excel Worksheet Functions | |||
| Replace a vlookup #N/A result with a value | Excel Worksheet Functions | |||
| Replace Vlookup with a macro | Excel Discussion (Misc queries) | |||
| How do I replace a return value of #N/A in a vlookup with zero? | Excel Worksheet Functions | |||
| When VLOOKUP returns a #N/A How can you get it to replace #N/A wi. | Excel Worksheet Functions | |||
Linear Mode
