One of the most powerful features within Excel are the LOOKUPS (VLOOKUP and HLOOKUP), they allow you pull values from other parts of the work book (or other work books) based on some lookup criteria. Here’s a quote from the Excel 2007 help section:

You can use the VLOOKUP function to search the first column of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or non-adjacent.) of cells, and then return a value from any cell on the same row of the range.

Unfortunately, the LOOKUPs only work with a Single lookup value, not when you have multiple criteria you want to match but here’s 3 examples of how I get around this limitation when I need to do a multiple criteria VLOOKUP: –

• Array Formula
• Creating a compound key + VLOOKUP
• SUMIFS (Unique Matches only)
• SQL – covered in another post

The example work book shown above can be downloaded here.