Microsoft Excel - VLookUp Function that looks for criteria #1 in the 1st column and then criteria #2

I would like to create a look up table in Excel that looks in column 1 of the table for criteria 1 and then looks for the second criteria and then a third criteria.  The reason for my inquiry is that I am trying to create a commission statement in Excel that first looks up a customer number and then a part number to determine if the part that the customer is purchasing is within an 18 month window from the date the first part was sold.  Our salesmen make a higher commission on new parts that are sold.  Once the part number has passed the 18 month window, the commission rate will drop to a lower amount.  We have many customers and sell many different parts to the customers.

Example: Customer # AAA001 has just purchased part XYZ that first shipped on 1/1/13.  The salesman will make a 5% commission on this part until 6/30/14.  Customer AAA001 may order part XYZ many times between 1/1/13 and 6/30/14.  On 7/1/14, the salesman will only make a 1.5% commission for part XYZ from customer AAA001.

Can Excel accomplish what I am trying to do?

Hi Tim,

A Vlookup can't do this, but you may be able to do something if you can create some unique criteria.

For example, you could add a formula to a cell that concatenates two or three criteria together, and you could perform a Vlookup against this.

E.g. Cell A1 =B1&C1&D1 (cells with your criteria) could look like AAA001XYZDate. So your Vlookup would only be against cell A1 rather than B1, C1 & D1.

This help?

Pete

Thank you for your reply.  I found a way of accomplishing the lookup by using INDEX and MATCH.

{INDEX('18 Month Customers'!$A$6:$C$540,MATCH(C4&H4,'18 Month Customers'!$A$6:$A$540&'18 Month Customers'!$B$6:$B$540,0),3)}

Hi Tim,

I'm glad you figured this out.

Doesn't the Index Match method only allow you to use two criteria rather than three?

Cheers

Pete

I was able to use an IF statement for the thrid criteria.