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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|