VLOOKUP where lookup_value is more than 1 colum

Hi renatonetmail,

Here's one way:

Please Login or Register  to view this content.

Note the length of the ranges (I used 1 to 10). You'll need to adjust this to cover the length of your employee list on Sheet2. Warning, the SUMPRODUCT function will not accept the entire column reference style of A:A.

Cheers,

Similar idea, but using the more robust INDEX/MATCH functions. In column C if Sheet1:

=INDEX(Sheet2!$C$1:$C$1000, MATCH(A2 & B2, INDEX(Sheet2!$A$1:$A$1000 & Sheet2!$B$1:$B$1000, 0), 0))

Originally Posted by ConneXionLost

Warning, the SUMPRODUCT function will not accept the entire column reference style of A:A.

This is a good point and is certainly true pre XL2007, not that you should ever use large ranges in any array based formula
(Sumproduct is processed in the same way as a standard CSE Array)

One other alternative which is very similar to JB's approach - ie set up to handle all data types re: result range (ie non-numeric ID's)

Please Login or Register  to view this content.