Great tricks! I’ve never tried the fuzzy matching plugin before. I’m curious though why you didn’t use FILTER() for #4 as a replacement for the IF-TEXTJOIN-SPLIT combo you built? If you want to lookup all values that meets a criteria, isn’t that what filter is for?
Thanks for great video. In the final example of commission what happens if the commissions are on sliding scales meaning for $96 sales the commission needs to be calculated as First $25 x 5% + $44 x 7%+24x10% etc. Is there a way to wrap that into one formula?
And, If I didn't use O365 and used regular excel, how can I use Vlookup with unadjusted data instead of ChooseCol function? Besides, how can we find last value in a range by using Lookup?
@GoodlyChandeep How to ungroup if you have this data table with column header "Date/Time" it was stack like this Date/Time Status. NAME Oct2 7:00 AM. Entry JAMES 8:00PM. Entry JOHN 9:20 PM. Exit JOHN Oct4 6:00 AM. Entry 8:20 Am 10:30 PM 12:10 PM Oct5 2:00 PM Oct9 1:00 AM 3:20 AM Somthing like that wanted to summarize it by date in power query
Tip 4 does not work in Excel 2021 because the TEXTSPLIT function is missing. Instead, the following shorter formula can be used. =TRANSPOSE (UNIQUE((FILTER(C$3:$C$14;$B$3:$B$14=E3))))
Although fuzzy lookup is a valuable tool, its implementation necessitates additional installation procedures. Conversely, Power Query provides a more streamlined and user-friendly approach. Developing solutions within an organisation becomes more challenging unless individuals have access to the most recent and advanced add-in. Notably, Power Query remains an underutilised feature in Excel for many users.
Download the Excel file - goodly.co.in/insanely-useful-lookup-tricks-in-excel
Join my weekly newsletter - goodly.co.in/subscribe/
More Excel Tricks - th-cam.com/video/j5BPpceK7UQ/w-d-xo.htmlsi=tqvZ8e-cathsMdit
@10:35 Bam ! This is what I was looking for, Brilliant as always..Great tip.
Thanks Chandeep. I liked fuzzy matching in PQ, but you can also use wildcards like *partlyname*.
Great video :) Thanks!
Great tricks! I’ve never tried the fuzzy matching plugin before. I’m curious though why you didn’t use FILTER() for #4 as a replacement for the IF-TEXTJOIN-SPLIT combo you built? If you want to lookup all values that meets a criteria, isn’t that what filter is for?
yes, exactly, filter is much better for that. And jointext + split text can be easily avoided with simple use of TRANSPOSE function.
Multiple results ia great!
Your video has made a real impact. Thank you❤
Excellent...thanks for sharing 🎉
Thanks for great video. In the final example of commission what happens if the commissions are on sliding scales meaning for $96 sales the commission needs to be calculated as First $25 x 5% + $44 x 7%+24x10% etc. Is there a way to wrap that into one formula?
Good job
This is absolutely crazy 😧
And,
If I didn't use O365 and used regular excel, how can I use Vlookup with unadjusted data instead of ChooseCol function?
Besides, how can we find last value in a range by using Lookup?
Addins not available in my workplace but great video as always
@GoodlyChandeep
How to ungroup if you have this data table with column header "Date/Time" it was stack like this
Date/Time Status. NAME
Oct2
7:00 AM. Entry JAMES
8:00PM. Entry JOHN
9:20 PM. Exit JOHN
Oct4
6:00 AM. Entry
8:20 Am
10:30 PM
12:10 PM
Oct5
2:00 PM
Oct9
1:00 AM
3:20 AM
Somthing like that wanted to summarize it by date in power query
Instead of IF formula, you could use FILTER formula. Much easier.
If is beat trick
Tip 4 does not work in Excel 2021 because the TEXTSPLIT function is missing.
Instead, the following shorter formula can be used.
=TRANSPOSE (UNIQUE((FILTER(C$3:$C$14;$B$3:$B$14=E3))))
Although fuzzy lookup is a valuable tool, its implementation necessitates additional installation procedures. Conversely, Power Query provides a more streamlined and user-friendly approach. Developing solutions within an organisation becomes more challenging unless individuals have access to the most recent and advanced add-in. Notably, Power Query remains an underutilised feature in Excel for many users.