How to lookup data while satisfying multiple conditions?

I had shown how to retrieve data from a database by looking up a single condition in my previous blog!
How to use VLOOKUP formula?

Many a times we come across situations where data retrieval depends on multiple conditions. A good example I can think of being a mechanical engineer is the selection of a screw. Screw is a threaded fastener. For one particular type of Screw the Thread/Inch and Length differs. 

Now I want to determine the inventory of a particular screw. For this I need to lookup all three values. But in Excel there is a provision to provide a single lookup value, or say a single cell reference.
So whats the solution? Simple! Combine the Screw size, Thread/Inch and the Length using a simple formula and let the result appear on a cell in the respective rows.

I have added the formula after the Length column. The result would be as shown below

I have combined the size, threads/inch and length values and separated each of them with a -

Now the next step is to create a Table to lookup the values from these database.

For that Go to the Sheet1 and rename it as LOOKUP ( you can rename anything you want). Create a table as shown, allowing the user to input data of the screw. Add a combine value cell using the same formula that I used before. Add the VLOOKUP formula under the diameter, inventory and price headers.

Thats about it! When you enter data in the input the formula gives out the respective values in the output if the data matches in the database.

For further refining your lookup table you could add drop down list of Inputs for the user to select. This will give a sophisticated look to your spreadsheet.

Leave your comments!

Keywords: Multiple Lookup, Vlookup for 2 or more conditions