Excel 2007 : VLOOKUP Help

[lofquist]

I am attaching a sample file.

This is the next step in my inventory project.

I am doing a vlookup to another tab to pull in the inventory on hand for a given part. The vlookup works fine unless there is not actually any inventory on had for that part. If that is the case it returns #N/A. Which then causes a problem with other formulas in the spreadsheet.

I'm not sure how to incorporate an IF statement which would return 0 if the parts were not found on hand.

Any help will be greatly appreciated.

Thanks,
Brian

[vlady]

hello
can't open your links but here's one = returns 0 if not available.

=IF(ISNA(VLOOKUP(A4,ONHAND,3,FALSE)),"",
IF(VLOOKUP(A4,ONHAND,3,FALSE)="","",
VLOOKUP(A4,ONHANDt,3,FALSE)))

[JBeaucaire]

You're using Excel 2007, so you can use IFERROR to suppress error results.

If your first formula were:

=VLOOKUP(A1, D:E, 2, 0)

....then you'd put that in an IFERROR like this:

=IFERROR(VLOOKUP(A1, D:E, 2, 0), "")


Now try that with your own VLOOKUP formulas.

[Marcol]

Given that you're using 2007 or above, you could just wrap your formula in IFERROR()

Please Login or Register  to view this content.

[EDIT]
Apologies Jerry, never saw your post
I used IFERROR(..........,0) because of the OPs' custom formatting, it returns "-" for zero.

[lofquist]

Thank you! That did it!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.