ISNA and VLOOKUP

[Guest]

I've got a dilemma with an Excel sheet. I have made several sheets which can
lookup from a master parts list (different worksheet in same workbook) for a
description, list price, and my cost based on the input of a part number.
Now I have one manufacturer who has over 100,000 part numbers. I have
combined their lists into one workbook, with 3 sheets of data. I want to
know if I can build a formula that will look at the first sheet, if it's not
found, look at the second sheet, and if it's not found look at the last
sheet, and return me the data I'm looking for.

I assume this is going to be a huge nested formula involving VLOOKUP and
ISNA, but I just can't put together the command in my head. Can anyone help?

If this works, my next challenge is to look up the same part number in
multiple sheets and return the lowest price. Any help with that would be
appreciated too.

 

[Arvi Laanemets]

Hi

It's time to switch to some database application obviously - probably Access
will do for start.


Arvi Laanemets

 

[Guest]

If I knew how to store my parts lists in Access and do lookups on it in
Excel, I would have done that already. But I do not know how to perform a
lookup in Excel that returns me certain data from a list in Access. If you
can help me learn how to do that, then I would love to use a database.

 

[Arvi Laanemets]

Hi

Move your whole project to Access - it suits you much better when your
tables have more then 10000 entries. Of course you can retrieve data from
Access into Excel, using ODBC query, but 65000+ limit for rows in Excel
remains, and so you must use some filters on Access table - as result your
Excel project will be cumbersome in design and for use. There is no simple
way for direct access to Access tables from Excel - maybe it's possible in
VBA, but I foresee a lot of heavy programming then, and nothing to gain,
what you don't get with much less effort in Access.

When you aren't familar with Access, then from my experience, writing the
code in Access is much easier for a rookie, compared with Excel - a lot of
wizards do much of work for you.


Arvi Laanemets