how do i replace #n/a in a vlookup?

  • Thread starter Thread starter infinite1013
  • Start date Start date
I

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)
 
Try

=IF(ISNA(VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE)),"",VLOOKUP(L2,'March
Chargebacks'!A$1:F$613,6,FALSE))

Mike
 
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.
 
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
 
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!
 
Glad I could help

infinite1013 said:
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!
Click to expand...
 
I was missing a parenthesis when transferring your formula
to my spreadsheet.
Click to expand...

Copy/Paste'ing into the Formula Bar (followed by removing any line feeds
produced by your newsreader) usually avoids that kind of problem.

Rick
 
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)),"",VLOOKUP(F2,BUYS!$F:$P,7,FALSE))

Any thoughts on how to get it to be a zero?
 
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
 
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")
 
=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)
 
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
 
The standard way is:
=if(iserror(yourformula),"",yourformula)

If you have Excel 2007, you can use:
=iferror(yourformula,"")

Regards,
Fred
 
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
 
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)
Click to expand...

=IFERROR(VLOOKUP(...), 0)
 
Can you please show me how to replace the #N/A result with 0? =VLOOKUP(L2,'March Chargebacks'!A$1:F$613,6,FALSE)
Click to expand...

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.
 
Back
Top