# Find the corresponding value from Multiple column match in Excel

1 month ago
4 Min Read
732 Words

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

### 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!

The Ultimate Excel Formulas Course

• includes XLOOKUP and will soon include Dynamic Arrays

Become a Power Pivot Hero

#### >> 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

Community Invitation

– Excel For All –

Decentralized and tokenized

Join today

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

Sign up for my newsletter – Don’t worry, I won't spam. Just useful Excel and Power BI tips and tricks to your inbox

Click here

Cross posted from my blog with SteemPress : https://theexcelclub.com/find-the-corresponding-value-from-multiple-column-match-in-excel/

0 comments