Two Field Lookup with Excel VLookup

I'm not a big fan of using Excel as a database application.  It wasn't designed to be one.  But some of the functions in Excel allow a lookup (or query, if you will) which leads some people to use it as a database.

The VLookup function encourages this thinking.  You pass in a lookup value, a data range, and the column that contains the value you want to retrieve from the data range.  There are some assumptions with the VLookup function. 

1. Your data must have the Primary Key or lookup column as the first column in the data range. 
2. The data must be sorted Ascending by the lookup column (first column)
3. The lookup column must contain single field, unique values

The VLookup has an optional parameter that lets you specify if you want an exact match or if you want Excel to choose the nearest value.

The problem I ran in to was that I needed to lookup a value based on two pieces of data.  The function is designed to only lookup based on a single value.

SOLUTION:

My solution was to combine my two data fields into a single TEXT field and use that value as the lookup value.  For example, my data was organized by Product ID in Column A and a Date in Column B.  Column C held a quantity value that related to a specific product on a specific date.

I first inserted a column at Column A.  This placed my Product ID in Column B, my Date in Column C, and my Quantity in Column D.  In Column A, I entered a formula to calculate my lookup values.  The formula is as follows for cell A2:

=TEXT(B2,"00000") & TEXT(C2,"yymmdd")

This formula converts my Product ID (which is a number value) to text and fixes at 5 digits with leading zeros if necessary.  The formula then concatenates the Date value which is also converted to text in the format of year month day.  So for Product ID = 1 and Date = June 22, 2007 my formula would return 00001070622.  Copying this formula for all rows of data produces my lookup values.

Next the whole data range must be sorted by Column A (lookup values).  Now the data range has been prepped for a two field lookup.

Whenever I want to query for a Quantity of a specific Product on a specific date, I would use a formula such as this:

=VLOOKUP( {cell with lookup formula}, {data range}, 4, FALSE)

NOTE:  I tried to create and lookup formula inside the VLOOKUP function but Excel kept giving an error.  So I created a formula outside of my report area that created the lookup value I needed to reference.  I used this formula:

=TEXT({cell with Product ID I needed},"00000") & TEXT({cell with Date I needed},"yymmdd")

{data range} was the sheet and range (or could be named range) where my data table was in the workbook.

The 4 represents Column D where my Quantity data is.  The FALSE in the formula tells Excel to make an exact match.  I don't want to be returned an approximate value -- I want to know the VLookup found the actual quantity I'm searching.