VLookup using multiple keys
VLookup is a very powerful tool for finding values in a table. If you have
never used it I recommend you find out about it as it adds a lot of power to
Excel. A common question related to vlookup is how to do a lookup on more than 1
key. For example: You have a table a like this:
| Plant | Code | Qty |
|---|
| 7071 | 70219 | 10 |
| 7072 | 12345 | 20 |
| 7073 | 67891 | 30 |
| 7074 | 70219 | 40 |
and you wish to lookup a value for Qty where Code = 70219 and Plant = 7074.
How do you do it? The answer is quite simple really.
Add a new column to the
left of the table which joins the 2 fields together using the & text joining
function and base the vlookup on that.
See the attached spreadsheet for a full
example of how this works.
For more complicated and powerful lookup functions why not checkout Chip Pearsons excellent page at http://www.cpearson.com/excel/lookups.htm
Chris Shepherd
| 