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!!!!!!
Download the workbook and take the Excel Data Challenge now - Find the corresponding value from a multiple column match in Excel
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
Your Solution
Now it's time to get your thinking cap on. How are you going to solve this Excel data challenge? Please do post a comment below and if you made a video with your solutions, please provide a link so we can all check it out.
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.
Take A FREE course with us Today!
- includes XLOOKUP and will soon include Dynamic Arrays
I would suggest that you sign up directly for your own hive wallet and use this to sign into the comments below. This way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet
>> GET HIVE WALLET NOW<<
Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can contact us with this link.
Like what you see? I do hope that you will share this article across your social profiles
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
Click here