Get rid of #N/A

[ipswichtaxis]

I've created a simple invoice worksheet which looks up prices of items from another worksheet.
I've copied and pasted the VLOOKUP formula into column H in worksheet1 which looks up the price of an item in a table created in worksheet2 when I enter the item name in column A of worksheet1.

This works perfectly when there's a value (item) typed into column A (worksheet1). However if there is no value in column A column H returns an error #N/A.

What I want is for the cell in column H to remain empty(blank) if the relative cell in Column A is empty(blank) without having to remove the embedded formula.

i.e. if cell A1 contains a product name (value) column H1 should look up and return the price from the table in worksheet2, which it does just fine.

but if cell A2 is empty I want cell H2 to also remain empty and not return #N/A.

Is there a workround for this.

here's my formula in column H

=VLOOKUP(A17,Sheet2!$A$2:$B$22,2,FALSE)


Thanks

[Haseeb Avarakkan]

One way is check first If A17 is in Sheet2!A2:A22 or not, if yes return VLOOKUP otherwise ""

=IF(COUNTIF(Sheet2!$A$2:$A$22,A17),VLOOKUP(A17,Sheet2!$A$2:$B$22,2,FALSE),"")

[jeffreybrown]

Try...

=IF(ISNA(VLOOKUP(A17,Sheet2!$A$2:$B$22,2,FALSE)),"",VLOOKUP(A17,Sheet2!$A$2:$B$22,2,FALSE))

[ipswichtaxis]

Thanks Guys both solutions solved the problem

[ipswichtaxis]

The original issue is resolved thanks, but it's created a new problem my totals column returns #VALUE! in empty cells.

Copy of worksheets attached any suggestions?

Thanks

[Haseeb Avarakkan]

=IF(N(H15),H15*G15,"")

copy down.

[ipswichtaxis]

=IF(N(H15),H15*G15,"")

copy down.

Tried that it returns 'FALSE' I would like cells to remain blank if there are no values in relating cells.

Thanks

[adaws]

Try....
= IF(ISERROR(VLOOKUP(A17,Sheet2!$A$2:$B$22,2,0)),"",VLOOKUP(A17,Sheet2!$A$2:$B$22,2,0)) or

=IF(ISBLANK(VLOOKUP(A17,Sheet2!$A$2:$B$22,2,0)),"",VLOOKUP(A17,Sheet2!$A$2:$B$22,2,0))

[ipswichtaxis]

Tried that it returns 'FALSE' I would like cells to remain blank if there are no values in relating cells.

Thanks

Sorry my bad it does work many thanks

[Haseeb Avarakkan]

See the attached, Also looks like you are on XL2007 or later, so you use IFERROR.

=IFERROR(your_formula,"")

will return blank if it is error.

[royUK]

When one problem is solved, mark it "Solved" then ask further questions in a new thread