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.

Coloring an Excel tab through VB Code

Recently, I had a project where I was populating more than 1 worksheet in an auto generated spreadsheet.  Setting the tab name is helpful but adding an alternate color really sets the tabs apart.

This post will show you how to do that.

To test this code, open a new Excel spreadsheet.

Press Alt-F11 to open the Visual Basic editor.

Click the menu Insert | Module

Paste the following code block:

[code]

Sub ColorTab()
   worksheets(1).Name = "New Tab Name"
   worksheets("New Tab Name").tab.color = RGB(255,0,0)
End Sub

[/code]

Switch back to the Excel Application and press Alt-F8 to bring up the Macro dialog box.  You should see the title of our new subroutine in the box.  Highlight ColorTab and press Run.

Your result should be that your first worksheet now is renamed and colored red.

 

EXPLANATION:

The renaming of the tab is fairly straight forward.  You can access the first worksheet by passing in the index of 1.  Rename the worksheet by passing the new name to the .Name property.

You can also access a particular worksheet by passing the tab name instead of the index.  To set the color of the tab use the .tab.color of the worksheet.  You must pass in a color value by using the RGB function.

The RGB function takes three integers between 0 and 255 representing a Red, Green, and Blue component.  The function results in a color value.

To help in creating the desired color combination, you can use varous tools to find the RGB values.  A tool I found online can be found here:  http://www.psyclops.com/tools/rgb/