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!!!!!! <h4><center><a href="https://www.dropbox.com/s/3o1xw0xmouq56cc/data%20challange%20-%20Multiple%20column%20match%20and%20lookup.xlsx?dl=0">Download the workbook and take the Excel Data Challenge now - Find the corresponding value from a multiple column match in Excel</a></center></h4> <h3>The Problem</h3> 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. <img src="https://theexcelclub.com/wp-content/uploads/2020/10/1_multiple-column-match-excel.png" alt="multiple column match excel" width="654" height="109"><br/> 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: <img src="https://theexcelclub.com/wp-content/uploads/2020/10/2.match-multiple-columns-in-excel.png" alt="match multiple columns in excel" width="743" height="60"><br/> 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 <h3>Your Solution</h3> 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. <h3>My Solution - Using Filter to do a multiple column match in Excel</h3> 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. <h3><center>Take A FREE course with us Today!</center></h3> <a href="https://courses.theexcelclub.com/courses/practical-beginner-excel-365"><img src="http://theexcelclub.com/wp-content/uploads/2019/01/beginner-excel-259x300.png" alt="FREE beginner excel training" width="259" height="300"><br/></a> <center>The Ultimate Excel Formulas Course</center></h3> * includes XLOOKUP and will soon include Dynamic Arrays <a href="https://courses.theexcelclub.com/courses/the-ultimate-excel-formulas-course-excel-365"><img src="http://theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course-254x300.png" alt="" width="254" height="300"><br/></a> <center>Become a Power Pivot Hero</center></h3> <a href="https://courses.theexcelclub.com/courses/power-pivot-excel-365-from-zero-to-hero" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?fit=441%2C513&ssl=1" sizes="(max-width: 441px) 100vw, 441px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?w=441&ssl=1 441w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=258%2C300&ssl=1 258w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=410%2C477&ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=100%2C116&ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=275%2C320&ssl=1 275w" alt="Power Pivot online training course" width="441" height="513" data-lazy-loaded="1"><br/></a> <center> </center></h3> https://youtu.be/NFz8XAIyuQg <h4>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</h4> <h4>>> <a href="https://hiveonboard.com/create-account?ref=paulag" target="_blank" rel="noreferrer noopener">GET HIVE WALLET NOW</a><<</h4> Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can <a style="font-family: var( --e-global-typography-text-font-family ); font-weight: var( --e-global-typography-text-font-weight ); background-color: #ffffff;" href="https://theexcelclub.newzenler.com/support/">contact us with this link</a>. Like what you see? I do hope that you will share this article across your social profiles <center>Community Invitation</center></h3> <center>β <a href="https://peakd.com/c/hive-102332/about">Excel For All</a> β</center></h3> <center>Decentralized and tokenized</center></h3> <center><a href="https://peakd.com/c/hive-102332/about">Join today</a></center></h3> Do you want to start collecting rewards quickly for learning Excel? Then you should try: <a href="https://theexcelclub.com/excel-learn-earn/">10+ Excel Learn and Earn Activities YOU can do Today</a> <center><strong>Sign up for my newsletter β Donβt worry, I won't spam. Just useful Excel and Power BI tips and tricks to your inbox</strong></center></h4> <a href="https://theexcelclub.newzenler.com/f/email-signup" role="button"> Click here </a> <br /><center><hr/><em>Cross posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : https://theexcelclub.com/find-the-corresponding-value-from-multiple-column-match-in-excel/ </em><hr/></center>
author | theexcelclub | ||||||
---|---|---|---|---|---|---|---|
permlink | findthecorrespondingvaluefrommultiplecolumnmatchinexcel-44kvhskwhkrsbcslggx1 | ||||||
category | excel | ||||||
json_metadata | {"community":"exxp","app":"exxp","image":[""],"tags":["excel","leofinance","stemgeeks","tutorials","coding"],"canonical_url":"https://theexcelclub.com/find-the-corresponding-value-from-multiple-column-match-in-excel/"} | ||||||
created | 2020-10-19 09:48:18 | ||||||
last_update | 2020-10-19 09:48:18 | ||||||
depth | 0 | ||||||
children | 5 | ||||||
last_payout | 2020-10-26 09:48:18 | ||||||
cashout_time | 1969-12-31 23:59:59 | ||||||
total_payout_value | 0.714 HBD | ||||||
curator_payout_value | 0.725 HBD | ||||||
pending_payout_value | 0.000 HBD | ||||||
promoted | 0.000 HBD | ||||||
body_length | 6,639 | ||||||
author_reputation | 49,417,421,433,400 | ||||||
root_title | "Find the corresponding value from Multiple column match in Excel" | ||||||
beneficiaries |
| ||||||
max_accepted_payout | 1,000,000.000 HBD | ||||||
percent_hbd | 10,000 | ||||||
post_id | 100,163,304 | ||||||
net_rshares | 7,444,756,976,693 | ||||||
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
onealfa | 0 | 76,681,696,048 | 3.88% | ||
nascimentoab | 0 | 4,554,832,188 | 100% | ||
steevc | 0 | 641,226,201,799 | 42% | ||
anarcho-andrei | 0 | 2,320,962,955 | 7.5% | ||
penguinpablo | 0 | 511,796,667,111 | 9% | ||
abh12345 | 0 | 476,654,025,138 | 20% | ||
funnyman | 0 | 152,059,374,401 | 3.6% | ||
slider2990 | 0 | 6,031,174,439 | 20% | ||
zorg67 | 0 | 600,924,076 | 100% | ||
edb | 0 | 26,566,809,167 | 21% | ||
travelnepal | 0 | 1,637,991,182 | 12.5% | ||
derosnec | 0 | 2,869,372,747 | 12.5% | ||
paulag | 0 | 1,254,422,469,751 | 100% | ||
evolved08gsr | 0 | 7,841,404,718 | 100% | ||
jayna | 0 | 13,509,172,658 | 4% | ||
guchtere | 0 | 2,792,529,920 | 12.5% | ||
edkarnie | 0 | 260,127,189,959 | 100% | ||
dernan | 0 | 849,658,036 | 100% | ||
zyx066 | 0 | 36,610,339,817 | 12.5% | ||
nascimentocb | 0 | 1,383,613,151 | 100% | ||
bashadow | 0 | 68,720,758,913 | 25% | ||
niallon11 | 0 | 11,237,001,559 | 1.25% | ||
jlsplatts | 0 | 9,346,398,179 | 1% | ||
meno | 0 | 245,986,347,507 | 12.5% | ||
investegg | 0 | 68,900,298,913 | 2.61% | ||
ankapolo | 0 | 2,933,900,872 | 5% | ||
angelro | 0 | 1,009,980,204 | 12.5% | ||
socent | 0 | 1,470,547,282 | 15% | ||
bluefinstudios | 0 | 720,880,121 | 1.25% | ||
kaniz | 0 | 1,376,656,681 | 50% | ||
tute | 0 | 131,927,238 | 100% | ||
pechichemena | 0 | 3,248,745,414 | 5% | ||
mytechtrail | 0 | 42,541,750,770 | 15% | ||
steeminer4up | 0 | 702,945,950 | 50% | ||
helpie | 0 | 61,288,764,622 | 25% | ||
markaustin | 0 | 792,562,530 | 5% | ||
cryptonized | 0 | 47,973,387,897 | 9% | ||
soulturtle | 0 | 620,695,242 | 5% | ||
nerdtopiade | 0 | 2,984,625,657 | 8.11% | ||
mountainjewel | 0 | 1,377,152,949 | 0.5% | ||
slacktmusic | 0 | 1,776,826,616 | 12.5% | ||
fragmentarion | 0 | 20,879,689,168 | 100% | ||
bennettitalia | 0 | 6,137,647,942 | 6.25% | ||
tryskele | 0 | 3,868,443,419 | 4% | ||
portugalcoin | 0 | 28,600,546,645 | 20% | ||
warnas | 0 | 38,538,310,742 | 10% | ||
philnewton | 0 | 661,952,534 | 10% | ||
jakeeyexe | 0 | 938,330,731 | 50% | ||
hazem91 | 0 | 2,974,586,809 | 3.75% | ||
zipporah | 0 | 12,769,891,308 | 5% | ||
doomsdaychassis | 0 | 16,703,231,397 | 25% | ||
onepercentbetter | 0 | 13,279,235,336 | 9% | ||
kanrat | 0 | 3,574,295,747 | 6.87% | ||
ivansnz | 0 | 333,767,608,429 | 100% | ||
racibo | 0 | 3,894,362,880 | 20% | ||
gadrian | 0 | 53,880,258,802 | 35% | ||
tonimontana | 0 | 3,525,551,260 | 41.83% | ||
jansher | 0 | 18,408,106,162 | 50% | ||
veckinon | 0 | 711,342,579 | 12.5% | ||
javicuesta | 0 | 775,472,585 | 25% | ||
bembelmaniac | 0 | 2,208,938,832 | 50% | ||
hafizullah | 0 | 73,567,725,419 | 40% | ||
kbr | 0 | 592,900,395 | 6.25% | ||
enforcer48 | 0 | 128,444,831,824 | 15% | ||
free-reign | 0 | 4,539,197,619 | 3.75% | ||
darrenclaxton | 0 | 585,135,377 | 2.5% | ||
frejafri | 0 | 941,495,636 | 2.5% | ||
kantos | 0 | 1,020,289,154 | 12.5% | ||
merlion | 0 | 3,962,626,441 | 9% | ||
deepu7 | 0 | 46,568,310,820 | 12% | ||
blewitt | 0 | 43,961,666,331 | 1.75% | ||
bflanagin | 0 | 22,687,031,639 | 12.5% | ||
melor9 | 0 | 1,829,135,136 | 12.5% | ||
lillywilton | 0 | 3,569,651,528 | 20% | ||
dalz | 0 | 157,468,562,863 | 40% | ||
zainenn | 0 | 15,686,562,482 | 12.5% | ||
raiseup | 0 | 46,564,015,348 | 9% | ||
voxmortis | 0 | 2,867,751,280 | 1.37% | ||
steemaction | 0 | 80,814,987,565 | 9% | ||
theexcelclub | 0 | 241,159,625,284 | 100% | ||
jessica.steem | 0 | 583,740,009 | 100% | ||
oakshieldholding | 0 | 298,046,116 | 100% | ||
balticbadger | 0 | 5,674,627,982 | 25% | ||
perfspots | 0 | 4,463,663,997 | 100% | ||
steempope | 0 | 495,582,708 | 70% | ||
deepsouthpiddlin | 0 | 3,833,367,813 | 20% | ||
eonwarp | 0 | 2,398,841,610 | 100% | ||
vibesforlife | 0 | 2,354,767,158 | 10% | ||
ancientknowled3 | 0 | 888,688,248 | 2.5% | ||
kaldewei | 0 | 1,562,159,860 | 6.87% | ||
helpiecake | 0 | 41,441,250,255 | 100% | ||
princessamber | 0 | 1,078,559,473 | 50% | ||
ctime | 0 | 391,607,298,545 | 2.5% | ||
helpie-caster | 0 | 7,240,706,643 | 13.75% | ||
ghostdylan | 0 | 1,082,897,883 | 50% | ||
dopal | 0 | 1,735,557,148 | 100% | ||
limka | 0 | 262,786,093 | 52.5% | ||
hungrybear | 0 | 363,285,424 | 9% | ||
samuel.steem | 0 | 932,155,166 | 100% | ||
kggymlife | 0 | 5,647,513,575 | 20% | ||
hungerstream | 0 | 1,039,339,025 | 50% | ||
gadrian-sp | 0 | 2,633,135,064 | 35% | ||
scholaris | 0 | 70,797,409,312 | 25% | ||
haxxdump | 0 | 46,648,440 | 5% | ||
tinyhousecryptos | 0 | 528,195,256 | 5% | ||
quantumdeveloper | 0 | 2,464,283,257 | 50% | ||
maruskina | 0 | 30,355,033,204 | 50% | ||
nalacanecorso | 0 | 1,180,358,766 | 100% | ||
stemgeeks | 0 | 7,974,909,047 | 50% | ||
freddio.sport | 0 | 7,182,823,183 | 50% | ||
stemcuration | 0 | 1,863,171,333 | 50% | ||
babytarazkp | 0 | 5,384,157,472 | 85% | ||
abh12345.stem | 0 | 676,856,116 | 100% | ||
vxc.stem | 0 | 0 | 41.83% | ||
urtrailer | 0 | 1,309,945,602 | 0.25% | ||
stemd | 0 | 336,241,507 | 100% | ||
joshmania | 0 | 9,784,915,417 | 6.25% | ||
tonimontana.leo | 0 | 117,792,945 | 1.76% | ||
abh12345.medi | 0 | 21,585,381 | 20% | ||
axel-blaze | 0 | 56,393,765,837 | 100% | ||
yggdrasil.laguna | 0 | 348,700,644 | 70% | ||
pecoshop | 0 | 1,826,253,575 | 100% | ||
policewala | 0 | 54,529,789,520 | 9% | ||
pjansen.leo | 0 | 40,489,088 | 50% | ||
stem.curate | 0 | 921,534,913 | 100% | ||
stemisaria | 0 | 1,873,496,085 | 100% | ||
hjmarseille | 0 | 10,172,676,193 | 70% | ||
boomalex | 0 | 2,426,285,632 | 100% | ||
im-ridd | 0 | 628,908,688 | 50% | ||
disagio.gang | 0 | 610,855,695 | 50% | ||
diyhub | 0 | 555,550,747,604 | 80% | ||
imfarhad | 0 | 25,045,333,664 | 40% | ||
peterpanpan | 0 | 100,784,385,697 | 100% | ||
meppij | 0 | 446,665,639,044 | 100% | ||
ciderjunkie | 0 | 2,653,481,832 | 6.87% | ||
localgrower | 0 | 1,601,157,461 | 3% | ||
beemd | 0 | 11,619,902,609 | 100% | ||
abdul.qadir | 0 | 0 | 5% | ||
intacto | 0 | 49,690,922,524 | 5% | ||
stemcur | 0 | 1,079,571,052 | 100% | ||
brofund-stem | 0 | 4,042,913,478 | 100% |
Nice challenge Paula. π I've done my solution with Power Query and, additionally added a Data model. How can I upload the file?
author | cristian.angyal | ||||||
---|---|---|---|---|---|---|---|
permlink | hpt1vxnucdhnkld | ||||||
category | excel | ||||||
json_metadata | {"app":"exxp/2.0"} | ||||||
created | 2020-10-20 13:02:30 | ||||||
last_update | 2020-10-20 13:02:30 | ||||||
depth | 1 | ||||||
children | 0 | ||||||
last_payout | 2020-10-27 13:02:30 | ||||||
cashout_time | 1969-12-31 23:59:59 | ||||||
total_payout_value | 0.120 HBD | ||||||
curator_payout_value | 0.122 HBD | ||||||
pending_payout_value | 0.000 HBD | ||||||
promoted | 0.000 HBD | ||||||
body_length | 128 | ||||||
author_reputation | 45,383,622,071 | ||||||
root_title | "Find the corresponding value from Multiple column match in Excel" | ||||||
beneficiaries |
| ||||||
max_accepted_payout | 1,000,000.000 HBD | ||||||
percent_hbd | 10,000 | ||||||
post_id | 100,179,776 | ||||||
net_rshares | 1,437,623,739,604 | ||||||
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
paulag | 0 | 1,216,187,918,416 | 100% | ||
theexcelclub | 0 | 221,435,821,188 | 100% |
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
author | cristian.angyal | ||||||
---|---|---|---|---|---|---|---|
permlink | po9n7pkxoo6j1t5 | ||||||
category | excel | ||||||
json_metadata | {"app":"exxp/2.0"} | ||||||
created | 2020-10-20 15:30:00 | ||||||
last_update | 2020-10-20 15:30:00 | ||||||
depth | 1 | ||||||
children | 1 | ||||||
last_payout | 2020-10-27 15:30:00 | ||||||
cashout_time | 1969-12-31 23:59:59 | ||||||
total_payout_value | 0.124 HBD | ||||||
curator_payout_value | 0.125 HBD | ||||||
pending_payout_value | 0.000 HBD | ||||||
promoted | 0.000 HBD | ||||||
body_length | 345 | ||||||
author_reputation | 45,383,622,071 | ||||||
root_title | "Find the corresponding value from Multiple column match in Excel" | ||||||
beneficiaries |
| ||||||
max_accepted_payout | 1,000,000.000 HBD | ||||||
percent_hbd | 10,000 | ||||||
post_id | 100,181,506 | ||||||
net_rshares | 1,466,928,073,029 | ||||||
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
paulag | 0 | 1,240,982,403,180 | 100% | ||
theexcelclub | 0 | 225,945,669,849 | 100% |
hay - well done. I was hoping someone would try this with Power query, nice work
author | paulag |
---|---|
permlink | re-cristianangyal-qilf0h |
category | excel |
json_metadata | {"tags":["excel"],"app":"peakd/2020.10.5"} |
created | 2020-10-22 07:48:21 |
last_update | 2020-10-22 07:48:21 |
depth | 2 |
children | 0 |
last_payout | 2020-10-29 07:48:21 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.000 HBD |
curator_payout_value | 0.000 HBD |
pending_payout_value | 0.000 HBD |
promoted | 0.000 HBD |
body_length | 81 |
author_reputation | 274,264,287,951,003 |
root_title | "Find the corresponding value from Multiple column match in Excel" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 HBD |
percent_hbd | 10,000 |
post_id | 100,203,811 |
net_rshares | 0 |
<div class="pull-right"><a href="https://steempeak.com/trending/hive-189641"><img src="https://cdn.steemitimages.com/DQmV9e1dikviiK47vokoSCH3WjuGWrd6PScpsgEL8JBEZp5/icon_comments.png"></a></div> ###### 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](https://peakd.com/trending/hive-189641) 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!
author | diyhub |
---|---|
permlink | re-findthecorrespondingvaluefrommultiplecolumnmatchinexcel-44kvhskwhkrsbcslggx1-20201021t065405z |
category | excel |
json_metadata | "{"app": "beem/0.24.9"}" |
created | 2020-10-21 06:54:06 |
last_update | 2020-10-21 06:54:06 |
depth | 1 |
children | 0 |
last_payout | 2020-10-28 06:54:06 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.000 HBD |
curator_payout_value | 0.000 HBD |
pending_payout_value | 0.000 HBD |
promoted | 0.000 HBD |
body_length | 1,041 |
author_reputation | 362,477,078,738,641 |
root_title | "Find the corresponding value from Multiple column match in Excel" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 HBD |
percent_hbd | 10,000 |
post_id | 100,190,206 |
net_rshares | 0 |
<div class="pull-right"><img src="https://cdn.steemitimages.com/DQmeHxGH67uYFvPfd76LGms358u21JRfqGFim6GN2CF5Dc4/image.png"></div> <div class="phishy"></div> It looks like this post contains one or more links that do not use a secure protocol (HTTP is in use instead of HTTPS). Be careful and do not enter sensitive information in that website as your data won't be encrypted. <br>More info on this service <a href="https://hive.blog/hive-192847/@keys-defender/new-features-extended-phishing-protection">here</a>. For more information on HTTP unsafety read: https://whynohttps.com https://web.dev/why-https-matters<br><sub>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 .
author | keys-defender |
---|---|
permlink | antiunsafelinks-keys-defender-bot-1603100902286 |
category | excel |
json_metadata | {"tags":["unsafelinks"],"app":"hivejs/kd"} |
created | 2020-10-19 09:48:24 |
last_update | 2020-10-19 09:48:24 |
depth | 1 |
children | 0 |
last_payout | 2020-10-26 09:48:24 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.000 HBD |
curator_payout_value | 0.000 HBD |
pending_payout_value | 0.000 HBD |
promoted | 0.000 HBD |
body_length | 774 |
author_reputation | 89,741,592,852,263 |
root_title | "Find the corresponding value from Multiple column match in Excel" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 HBD |
percent_hbd | 10,000 |
post_id | 100,163,305 |
net_rshares | 0 |