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
Cross posted from my blog with SteemPress : https://theexcelclub.com/find-the-corresponding-value-from-multiple-column-match-in-excel/
Please download the file to check my Power Query and Power Pivot solution: there are actually 2 solutions inside: one with PQ to get to the same result as Paula and one Data Model and some Pivots and Slicer in order to Analyze the data, not only to get it in raw form.
File link here: https://1drv.ms/x/s!AoqYOey0DYOTjQRHfsciuKuAcxHZ?e=AqvaPu
hay - well done. I was hoping someone would try this with Power query, nice work
Nice challenge Paula. 👍
I've done my solution with Power Query and, additionally added a Data model.
How can I upload the file?
More info on this service here. For more information on HTTP unsafety read: https://whynohttps.com https://web.dev/why-https-matters
This auto-reply is self-voted to be more visible among others. These messages are throttled 1/10. If this message bothers you reply OFF .
Thank you for sharing this amazing post on HIVE!
Your content got selected by our fellow curator @tibfox & you just received a little thank you via an upvote from our non-profit curation initiative!
You will be featured in one of our recurring curation compilations and on our pinterest boards! Both are aiming to offer you a stage to widen your audience within and outside of the DIY scene of hive.
Join the official DIYHub community on HIVE and show us more of your amazing work and feel free to connect with us and other DIYers via our discord server: https://discord.io/diyhub!
If you want to support our goal to motivate other DIY/art/music/homesteading/... creators just delegate to us and earn 100% of your curation rewards!
Stay creative & hive on!