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


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

Advertisements

M

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
 
I

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.
 
M

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
 
I

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!
 
M

Mike H

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!
 
Ad

Advertisements

R

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
 
S

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?
 
P

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
 
Ad

Advertisements

R

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

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

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
 
F

Fred Smith

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

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

Regards,
Fred
 
G

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
 
Ad

Advertisements

G

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

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top