Two Lookup Values?

[Bob Cresto]

I have a worksheet with columns for Dates-Salesman-Customer and another
worksheet with the distance between each salesman and customer. Each
salesman will go to several different customers and more than one salesman
will go to each customer. I want to bring the distance from the other
worksheet over to the first worksheet but it requires me to have two lookup
values, Salesman and Customer. Can you have two lookup values in a Vlookup
formula??

Thanks,
Bob

 

[Ken Wright]

=SUMPRODUCT((RngA=Salesman_Cell)*(RngB=Customer_Cell)*(RngC))

RngA = List of Salesman on your Distance/Salesman/Cust sheet
RngB = List of Customer on your Distance/Salesman/Cust sheet
RngA = List of Distances on your Distance/Salesman/Cust sheet

RngA=RngB=RngC in SIZE, ie if one is 50 cells long, then they are all 50 cells
long

 

[Guest]

Hi Bob

I don't think you can have 2 lookup values in a simple lookup formula but a simple work-around would be to create another column concatenating Salesman and Customer to the left of the table you are looking into
For example if you had Salesman 'Smith' in cell B1 and customer 'Jones in cell C1, put the formula '=B1&C1' in cell A1, this will return 'SmithJones' and will be a unique identifier for your VLOOKUP formula. (Make sure you are looking for a Salesman & Customer combination, but this concatenation can be done directly in the Vlookup formula).

 

[Bob Cresto]

You guys are the best!

Thanks again,
Bob

 

[Ken Wright]

And you are more than welcome