Microsoft Access Office VB VBA Help and Examples
           

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:

PlantCodeQty
70717021910
70721234520
70736789130
70747021940

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

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.