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

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

 

[Mike H]

Try

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

Mike

 

[infinite1013]

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]

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]

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]

Glad I could help

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!

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Steve]

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?

 

[Pete_UK]

Change the "" to a zero in the middle of the formula.

Hope this helps.

Pete

 

[Steve]

Perfect thank you

Change the "" to a zero in the middle of the formula.

Hope this helps.

Pete

 

[Pete_UK]

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

 

[Richard]

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

 

[Dave Peterson]

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

 

[Casper]

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

 

[Fred Smith]

The standard way is:
=if(iserror(yourformula),"",yourformula)

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

Regards,
Fred

 

[Gord Dibben]

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

 

[gerald.hemelmayr]

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)

 

[daveinau]

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.