As the #N/A is the result of a formula, Replace wouldnt work anyway.
As the "true" part of your Iserror() function, return 0 if the formula evaluates to an error.
This is a discussion on remove #N/A in excel within the Excel Questions forums, part of the Question Forums category; Hi, i am using these formula below; =IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)), VLOOKUP(B2,Sheet2!A:F,2,FALSE),VLOOKUP(A2,Sheet2!A:F,2,FALSE)) sheet 1 Item Alternative Items Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 12345 ...
Hi,
i am using these formula below;
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),
VLOOKUP(B2,Sheet2!A:F,2,FALSE),VLOOKUP(A2,Sheet2!A:F,2,FALSE))
sheet 1
Item Alternative Items Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 12345 #N/A #N/A #N/A #N/A #N/A 12398 #N/A #N/A #N/A #N/A #N/A 12455 45678 0 0 0 0 0 004A166 #N/A #N/A #N/A #N/A #N/A 004A167 #N/A #N/A #N/A #N/A #N/A 00510R0810 510R081 0 0 0 0 0 00675R0140 675R014 0 0 0 0 0
sheet 2
Item Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 12345 #N/A #N/A #N/A #N/A #N/A 12378 #N/A #N/A #N/A #N/A #N/A 45678 0 0 0 0 0 004A166 #N/A #N/A #N/A #N/A #N/A 004A167 #N/A #N/A #N/A #N/A #N/A 510R081 0 0 0 0 0 675R014 0 0 0 0 0
how can i remove the #N/A without using Replace?
Thanks
As the #N/A is the result of a formula, Replace wouldnt work anyway.
As the "true" part of your Iserror() function, return 0 if the formula evaluates to an error.
Yesterday I felt on top of the world. Today its falling in on me.
If the formula is expected to return a number, try on Excel versions prior to 2007...
If so desired, custom format the formula cell as:Code:=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0, VLOOKUP(B2,Sheet2!A:F,2,0),VLOOKUP(A2,Sheet2!A:F,2,0)))
[=0]"";General
Assuming too much and qualifying too much are two faces of the same problem.
Hi Aladin,
i tried the formula you gave.
lookup(9.99999999999999E+307,CHOOSE((1,2,3,),0,VLOOKUP(B2,Sheet2!A:F,2,0),VLOOKUP(Sheet1!A2,Sheet2!A:F,2,0)))
there is an error that say
"The formula that you typed contains an error."
Is there another formula that you can recommend?
thanks,
Your original formula basically says that if it can't find A2 in your lookup table, then try looking up B2.
So the only time you would be getting #N/As is if it cannot find B2 in your lookup table either.
Someone will probably add something less cumbersome but you should be able to add another error trap inside your error trap.
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),
IF(ISERROR(VLOOKUP(B2,Sheet2!A:F,2,FALSE)),"ERROR",VLOOKUP(B2,Sheet2!A:F,2,FALSE)),VLOOKUP(A2,Sheet2!A:F,2,FALSE))
Should return the text string of "Error" when neither A2 or B2 appear in your lookup tables. Much easier to replace. Or you can make it return blank ("") or whatever you like.
---------------------------
Yes I know there are better ways to do it.
My brain is a Double. Add in too many values and it Overflows.
hi Beezkneez,
i tried your formula there is an error that says "you've entered to many arguments for this function".
what should i do?
Sorry i'm not that good in excel.
Thanks.
try this in the original sheet
If(iserror(your formula),"",your formula)
hi yogeshmaney,
i used the formula below;
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),"",VLOOKUP(B2,Sheet2!A:F,2,FALSE))
and the result is this (i used the formula in Jun-09 only);
Item Alternative Items Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 12345 #N/A #N/A #N/A #N/A 12398 #N/A #N/A #N/A #N/A 12455 45678 0 0 0 0 004A166 #N/A #N/A #N/A #N/A 004A167 #N/A #N/A #N/A #N/A 00510R0810 510R081 0 0 0 0 00675R0140 675R014 0 0 0 0
but when i added a value on sheet 2 (see below table), the item 12455 should have a value of 12. there should be a value in Jun-09 which is 12.
Item Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 12345 12378 45678 12 0 0 0 0 004A166 004A167 510R081 0 0 0 0 0 675R014 0 0 0 0 0
thanks,
aivi
your formula:
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),"",VLOOKUP(B2,Sheet2!A:F,2,FALSE))
says if there's an error looking for A2, then try B2. So you need to capture an error looking for B2, as well:
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),"",IF(ISERROR(VLOOKUP(B2,Sheet2!A:F,2,FALSE)),"",VLOOKUP(B2,Sheet2!A:F,2,FALSE)))
I want Excel and Access to get married and have a baby. And I would call that baby 'Accel'
Windows 7, Office 2010
Like this thread? Share it with others