I

#### infinite1013

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

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

I

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

Ad

M

=IF(ISNA(VLOOKUP(L2,'March

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

Chargebacks'!A$1:F$613,6,FALSE))

Mike

I

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

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

spreadsheet. It works perfect now! Just what i needed. Thanks so much!

M

infinite1013 said:

spreadsheet. It works perfect now! Just what i needed. Thanks so much!

Ad

R

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

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

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

Hope this helps.

Pete

Hope this helps.

Pete

S

Pete_UK said:Change the "" to a zero in the middle of the formula.

Hope this helps.

Pete

P

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

R

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

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

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

=if(iserror(yourformula),"",yourformula)

If you have Excel 2007, you can use:

=iferror(yourformula,"")

Regards,

Fred

G

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

G

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

=IFERROR(VLOOKUP(...), 0)

Ad

D

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.

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