Find the corresponding value from Multiple column match in Excel

Do you fancy a bit of an Excel Data Challenge?  Something to get you thinking creatively with your Excel Skills?  Well, I have this problem that requires a multiple column match in Excel to look up and return a corresponding value and I think you will enjoy solving it!

I received a spreadsheet from my brother last week and he was trying to solve a complex lookup.  It involved matching a number of columns from one table to return an appropriate rate.  Sometimes I wonder why people structure their data in such a way it creates a headache to work with.  But anyway, I still love you bro!!!!!!

The Problem

A record of Employee Charge out rates is kept in the following format on one worksheet.  It records the employee name, to and from dates, a code that relates to a client, and then the corresponding rates.

On the Employee Charge out rates sheet, each employee can be listed multiple times.  They can be more than once on the same client code with different rates for different dates. Or they could also be on different clients on the same day and different rates could apply here too.

On a separate worksheet we have timesheet entry details as follows:

This sheet records the employee name, the date, the client code, the number of hours, and the class.  The class refers to the rate which is to be paid.  There are 3 classes, Standard, T1.5, and T2

We need to create a formula that will look up the appropriate rate from the Employee Charge out rates and return into the column in the timesheets entry sheet.

To do this, we need to match appropriate the Name and Code from the timesheet to the charge-out rates, ensuring the date on the timesheet is between the from and to dates on the charge out and return the correct rate based on the specified Class

My Solution – Using Filter to do a multiple column match in Excel

I considered many options, including the use of helper columns and Power Query.  But instead, I went with a combination of an IF statement and the Filter Function.  In this video, we will first discuss the problem and the requirements and then at around 4:45, I get stuck into providing my solution.

The Ultimate Excel Formulas Course

* includes XLOOKUP and will soon include Dynamic Arrays

Join today

Do you want to start collecting rewards quickly for learning Excel?  Then you should try:

10+ Excel Learn and Earn Activities YOU can do Today