Catalog # lookup

This is going to start as a simple vlookup, match, hlookup, but it has a hitch.
I have about 500 different catalog numbers but there are 3 different ranges depending on size and each range has rows marked as color and columns marked as quantity.

A large widget might be 2 colors at a quantity of 5000. I need to enter the cat# and quantity with a return of price and catalog description.

A sample spreadsheet is attached. Tables are on the first page and catalog samples on second.

Is it possible.

Thanks

How is the 14,000 calculated?

I didn't put the formulas in, just the answers.
My biggest challenge is the color part. I need to somehow have each lookup not only determine size and quantity but also color. I am wondering if it wouldn't help to put a column in the catalog table to represent # of colors???

Originally Posted by bebo021999

Try in D21:

Please Login or Register  to view this content.

In C21:

Please Login or Register  to view this content.

Thank you, it's working on the small sheet now I have to try it on the real thing.
I'll keep you posted.

Thanks for the help. Your suggestions did in fact work. I have decided to turn to youtube and learn this stuff for myself.