Easiest Lookup formulas for any situation - an interview with Oz Du Soliel (Excel on Fire)
HTML-код
- Опубликовано: 20 июл 2024
- Ever wondered which lookup formula to use given a situation? Me too. So I asked Oz to tell me how to get lookups right. In this informative interview, Oz talks about:
1) When to use VLOOKUP
2) When to use XLOOKUP
3) When to avoid lookup formulas and use DA functions like FILTER()
4) When to use Power Query
We will be looking at 5 most common scenarios for lookups in business data situations and analysing them.
These are
1) Simple lookups
2) Lookup from a column in the middle
3) Two way lookup
4) Lookup based on multiple conditions
5) Lookup and return all matching results
Video topics:
0:00 - Introduction
1:23 - Oz on stage
4:24 - A quick look at the data and five scenarios
5:31 - Simple lookup
7:18 - Lookup in the middle - based on a column not on left
9:51 - Two way lookup (both column and row based conditions)
12:09 - Multiple conditions in lookups
15:22 - Multiple matching results with lookup formulas
17:45 - Power Query for getting multiple results or multiple conditions
20:00 - Closing remarks on what attitude we need to have to build necessary data skills
Links & Resources:
===============
Workbook from the video:
chandoo.org/wp/wp-content/upl...
Oz's Excel on Fire Channel - / walruscandy
Chris Penn's article mentioned by Oz - www.christopherspenn.com/2017...
#MsExcel #VLOOKUP #XLOOKUP
Sound effects from zapsplat.com 
Наука
Thanks Oz for doing this and sharing your wisdom with all of us. 🙏
Please download the sample file from here 👉 - chandoo.org/wp/wp-content/uploads/2020/07/best-lookups-with-oz.xlsx
Important timestamps -
1:23 - Oz on stage
4:24 - A quick look at the data and five scenarios
5:31 - Problem 1 - Simple lookup
7:18 - Problem 2 - Lookup in the middle - based on a column not on left
9:51 - Problem 3 - Two way lookup (both column and row based conditions)
12:09 - Problem 4 - Multiple conditions in lookups
15:22 - Problem 5 - Multiple matching results with lookup formulas
17:45 - Bonus - Power Query for getting multiple results or multiple conditions
Hi Guys.. thanks for the fun discussion about Lookups and the various options. XLOOKUP and FILTER really bring something new to the party. But, the old standards like VLOOKUP and INDEX/MATCH can still get the job done. And, Power Query can be the best option, particularly with ugly data that needs cleaning. Thanks for the insights.. always a pleasure to watch you two work :)) Thumbs up!!
Thanks Wayne. That is the point too... Whatever function works for your situation, just go with it. No need to overthink :)
WAYNE!
YUP! Go with whatever works. No client is ever going to complain that you didn't use something fancy or new.
Woahhhh!! I'm super excited for this video!!
Caroline, I hope we delivered. :)
Awesome intro..... Eager to watch it
Yeah! Chandoo and Oz.. bring it on!! :))
It's coming! :)
Oz and chandoo sir thanks for your detailed video about lookups.
My pleasure
You are so very welcome!
Thanks Chandoo to invite Oz. This is really easy and simple session. Sometime basic stuff is better then complicated creation.
As we have a discussion longtime ago invite Excel expert so this is 2nd episode.
Who is next.
Take Care
Thanks for sharing knowledge.
Ali! Thank you.
I also eager to watch it
Yeah! 🤘
HELL YEAH
Both of you my all-time favorite just quick question on PDF data import and extract as per our creterial
like I have my ATT or Verizon bill then if suppose I would need data from invoce like.
Invoice Number
Invoice Date
Summary of Charges.
etc.
Thank you Nasim.. you can use Excel 365's Power Query > from PDF option for this. Give it a try.
Thanks Nassim. :)
Are these function available in Excel 2019?
Unfortunately no. All new functions like XLOOKUP, FILTER etc are only available in Office 365.
Lovely 🌹 guys 💕
Thanks 🤗