XLOOKUP but wrong results?!?
Рет қаралды 25,141
Күн бұрын💥Get my XLOOKUP Course ~ chandoo.org/wp/lookups-for-da...
Time for a tricky XLOOKUP (or just VLOOKUP problem). I have with me a product price list with a twist. There is an effective date for the price. So if I try to lookup for a product, my XLOOKUP (and VLOOKUP, as well as INDEX MATCH) results are wrong.
If there is one thing at Awesome Chocolates that we hate, it is the Crumbly Cookies. They are our competitor. But we also hate the wrong formulas. So let's get fixing. 🛠
In this video ⏱👇:
===============
0:00 - The problem
2:05 - One way to solve the problem (with MAXIFS & FILTER)
4:05 - Using XLOOKUP to solve the problem
5:08 - Moral of the story
Sample workbook 🟩👉
===================
Please download the sample workbook here - chandoo.org/wp/wp-content/upl...
and practice the concepts.
💥Get my XLOOKUP Course
=======================
Learn and master XLOOKUP, INDEX+MATCH, 2-way lookups, multi-condition lookups and more powerful techniques from my comprehensive XLOOKUP course - chandoo.org/wp/lookups-for-da...
You can also watch this course for free if you are SKILLSHARE member, here 👉 skl.sh/3hbR49d
More videos on XLOOKUP & Other lookups:
===================================
What is XLOOKUP and how to use it? • I don't use VLOOKUP an...
What is FILTER and how to use it? • I don’t use filters in...
What is VLOOKUP and how to use it? • Excel VLOOKUP - Compl...
What is INDEX MATCH and how to use it? • Excel's INDEX + MATCH ...
What is the best way to lookup things in Excel? • Easiest Lookup formula...
5 Advanced VLOOKUP Tricks - • 5 *Advanced* lookup tr...
~
You have a beautiful day 😀🌼🌞
#xlookup #exceltips
@vijayarjunwadkar 2 жыл бұрын
Yet another useful video! Love the practicality of your solutions, and your easy way of putting it together for anybody to understand! Cheers! 😊👍
@chandoo_ 2 жыл бұрын
Thanks Vijay... I am glad you enjoyed this (and many other videos :))
@JoseAntonioMorato 2 жыл бұрын
Dear Chandoo, The XLOOKUP formula can be done like this too: =PROCX(H5&H12;C5:C28&D5:D28;E5:E28) or =XLOOKUP(H5&MAXIFS(D5:D28,C5:C28,H5),C5:C28&D5:D28,E5:E28)🤗
@chandoo_ 2 жыл бұрын
Good one Jose. Donut for you 🍩
@chrism9037 2 жыл бұрын
Another great video! Keep up the great work, Chandoo!
@chandoo_ 2 жыл бұрын
You are welcome Chris 😀😀😀
@wayneedmondson1065 2 жыл бұрын
Awesome Chandoo! Thanks for the lookup tricks. Thumbs up!!
@chandoo_ 2 жыл бұрын
You are welcome Wayne :)
@salehmuhammad6811 2 жыл бұрын
Chandoo has always alternate options to solve any problem. This shows nothing but sheer brilliance 👍👌👍
@chandoo_ 2 жыл бұрын
As Steve Jobs said, "There is always one more thing" 😀
@rautoramarautorama9324 2 жыл бұрын
Why lookup value in the xlookup is 1? 4:15
@tuyoexcelypowerbi 2 жыл бұрын
I wonder it as well
@Seftehandle 2 жыл бұрын
Me 3
@chandoo_ 2 жыл бұрын
When you take the conditions (products list = product) and multiply it with (dates list = latest date), you are essentially multiplying two boolean arrays (bunch of true/falses with another bunch of true/falses). This will result in an array of 1s & 0s. 1 where both conditions are true 0 when either condition is false So when we xlookup 1, we are looking for first combination where product & dates match. Hope that helps.
@relax9286 2 жыл бұрын
Thank you Chandoo!
@primeknowledge99 2 жыл бұрын
Amazed as always 🙂 thanks a million sir
@chandoo_ 2 жыл бұрын
Most welcome!
@alexkerr7436 2 жыл бұрын
This is great. When I go back to work on Monday, I'm going to delete some embarrassing helper columns (whole columns of COUNTIFS), which I don't need anymore. Thank you Chandoo.
@chandoo_ 2 жыл бұрын
That's a great idea!
@erifirman9556 2 жыл бұрын
You're very inspiring me. Thanks sir👍
@arjundev4908 2 жыл бұрын
Chandoo Sir, I need a carbon copy of your brain. Honestly!!
@chandoo_ 2 жыл бұрын
🧠🧠🧠
@MrBeograd1997 2 жыл бұрын
BEAUTIFUL!!!!
@chandoo_ 2 жыл бұрын
Thank you :)
@balamira297 2 жыл бұрын
Baagaundhi andi ! Learnt a new thing today
@chandoo_ 2 жыл бұрын
So welcome Mira 😍
@A_Proud_Indian 2 жыл бұрын
We can use sort function in xlookup also
@nagarapusudheeksha5750 2 жыл бұрын
A big thumbs up for 👍👍👍👍👍🙏 saviour .... 💓
@chandoo_ 2 жыл бұрын
You are welcome Sudheeksha...
@ranaganguli2892 2 жыл бұрын
It's awesome 👌
@chandoo_ 2 жыл бұрын
You are welcome Rana...
@officialpreside10 2 жыл бұрын
Never a regret Subscribing to ur channel. Weldone Sir.
@chandoo_ 2 жыл бұрын
So nice of you
@officialpreside10 2 жыл бұрын
@@chandoo_ Sir is there a way anyone can get excel 365 version without purchase? As some may not be able to afford the subscription.
@chandoo_ 2 жыл бұрын
You can use Excel online to practice few concepts. Not sure if it is free. If you are a student, you can purchase the student license for cheap. Alternatively, see if you can share the office online with family members. You get 5 licenses per purchase. So when you split it with some siblings / cousins the cost may be less.
@abhiabhishek7942 Жыл бұрын
Hi bro, I really enjoy your videos it has helped me in my professional career. Thank you for your time fir making these informative videos. My 1 request you to make a video on business metrics like kpi dashboard where a manager can see the complete report on one place and if can provide a free template to fill the data as per our use. I will be grateful for your help. Will be waiting for this video. Thanks in advance.
@canirmalchoudhary8173 2 жыл бұрын
Hey chandoo you could use concatenated lookup value and concatenated lookup array of Milk bars and date. I found this as easy than filter
@chandoo_ 2 жыл бұрын
Good suggestion. We would still need to calculate the latest date. I try not to add columns as that can be another problem to fix when data changes or grows.
@sagatsudrik2144 Жыл бұрын
Hello chandoo, refer formula like - =lookup(2,1/(range lookup=lookup value),result vector) just try this
@Seftehandle 2 жыл бұрын
Very useful video. My question from the data will be how can you find which bars changed price and when at once? Thanks!
@chandoo_ 2 жыл бұрын
Thanks Tina. Interesting question there. You can use this formula see a list of all products that had at least one price change. =UNIQUE(FILTER(C5:C28,COUNTIFS(C5:C28,C5:C28)>1)) Assumes C5:C28 has your product list. If you want the reverse (ie, which products haven't had a price change), you can use this: =UNIQUE(C5:C28,,TRUE)
@Kingx7565 2 жыл бұрын
We can also get this answer using Sumproduct function: =Sumproduct(--(C5:C28=H5)*(D5:D28=H12),E5:E28)
@chandoo_ 2 жыл бұрын
YES!!! Or even SUMIFS... Both work as long as the return column has numbers
@JoseAntonioMorato 2 жыл бұрын
Your idea is very good, but it can also include the MAXIFS function and eliminate cell H12: =SUMPRODUCT(--(C5:C28=H5)*(D5:D28=MAXIFS(D5:D28;C5:C28;H5));E5:E28) 🤗
@Excelambda 2 жыл бұрын
Great video!! Cool trick XLOOKUP(1,.... If some price is changed twice on the last date and the data set is not in chronological order, to return both matches FILTER formula in H3 does the trick.✌ Or we can use NOW() instead of TODAY() on the date column when we operate price changes and both filter and xlookup will return a single result.😉
@TSSC 2 жыл бұрын
Right! Date risks not being unique. Awesome Chocolate should redesign their price database so that it includes a row-unique ID. Date and time could do, but probably only with sufficient granularity for the time part. Then they could ship a box or two to us, don’t you agree? 😉
@Excelambda 2 жыл бұрын
@@TSSC Hi, great points!! When we fill out some table or array with some records, there are ways to register on the date field NOW() and keep them from not recalculate, since NOW() is volatile. This can be done with formulas using circular references or very simple VBA. If we have this kind of data set NOW() has granularity to the second so no date dups will occur. Or, if we set the date manually including time with seconds, also will have no dups. Regarding the present box, as long as they are sugar free, no problem. ✌😉😃
@JJ_TheGreat 2 жыл бұрын
Question: why is 1 the first argument in the XLOOKUP() version? Thanks.
@Excelambda 2 жыл бұрын
@@JJ_TheGreat The formula =XLOOKUP(1,(C5:C28=H5)*(D5:D28=H12),E5:E28) lookup array (C5:C28=H5)*(D5:D28=H12) Only when both conditions are met will have TRUE*TRUE=1, (AND logic or boolean multiplication) If one or both conditions will deliver a FALSE , FALSE*TRUE or TRUE*FALSE or FALSE*FALSE = 0 In other words, we are looking for 1 because we are looking for the correspondent values of return array E5:E28 when both conditions of lookup array are met.
@JJ_TheGreat 2 жыл бұрын
@@Excelambda Great, that makes sense. Thank you!
@amazon5665 Жыл бұрын
what if want a variable to get from 2 sheets, and the variable is mention in 2 sheets..how to average them.
@Tina-gp4rg 2 жыл бұрын
Thank you for your video. Could you please make a video with useful reports in accounting? Real case scenario? I would highly appreciate it. Thank you very much.
@chandoo_ 2 жыл бұрын
Great suggestion! I will do some research on this topic and make a video in coming months.
@sohailali5103 2 жыл бұрын
Sir please make some video on job opening in this field
@jagatkrishna1543 2 жыл бұрын
YES Sir 🙏💕💲
@chandoo_ 2 жыл бұрын
Thanks JK... 😀
@babarqureshi5043 2 жыл бұрын
Thanks Sir.
@chandoo_ 2 жыл бұрын
You are welcome Babar.
@surajbhardwaj428 2 жыл бұрын
Really like... Yes I want to join course...
@chandoo_ 2 жыл бұрын
You are welcome brother...
@ananda141987 3 ай бұрын
I have used this xlookup trick in a table also used index match which refer column name in formula to fetch data from one table to another table it is giving a # ref for index match and and error for xlookup kindly guide.
@pv8929 Жыл бұрын
In xlookup why did you add "1" at the start?
@UgoCynthia 2 жыл бұрын
Hi, how do I get into your class room, i have interest of changing my career into data analysis and everything is new to me, please can you teach me? Thanks
@vishnuvardanatmakuri 2 жыл бұрын
Good video Chandoo, thank you. I have slightly different problem. My price list keeps changing over a period of time and i pull prices from my source table, but I don't want to get the latest price but get the prevailing price while the order was placed, please suggest a way to get the price of the product at that time period. Thank you!!
@chandoo_ 2 жыл бұрын
Thanks Vishnu. You can change the maxifs() to look for the most recent price change on or before a date. For example, to see the latest price of Almond Choco as of 21st of February, 2022, I can use this: =MAXIFS(D5:D28,C5:C28,H5,D5:D28,"
@hasanmohammadtareq89 Жыл бұрын
Dear @Chandoo Bhai, I wrote the following formula on your sheet and found the same result: =XLOOKUP(H5&H12,C:C&D:D,E:E)
@tuyoexcelypowerbi 2 жыл бұрын
Why lookup value is 1?
@warrenheaps1955 2 жыл бұрын
There are two true-false conditions. Each will be either true (1) or false (0). They are multiplied so if both conditions are true the value will be 1 x 1. So the first 1 matches the conditions if they are both true.
@tuyoexcelypowerbi 2 жыл бұрын
@@warrenheaps1955 many thanks!!!!!
@chandoo_ 2 жыл бұрын
Thanks Warren. Donut for you 🍩
@warrenheaps1955 2 жыл бұрын
I did not know how to use XLOOKUP with multiple conditions - this will be handy. Thanks for sharing.
@e.sreedharan110 2 жыл бұрын
i think you filter table options to select latest data of month only, then work your xlookup formulas then you get latest value update
@chandoo_ 2 жыл бұрын
As mentioned in the video, not all products change each month. So this method won't work. Plus, Excel formulas just use all the data, not just filtered portions alone.
@hellboy0013 2 жыл бұрын
What is the equivalent of vlookup + match function under xlookup?
@chandoo_ 2 жыл бұрын
Hmm.. can you elaborate? What is the VLOOKUP + MATCH? What are you trying to do?
@hellboy0013 2 жыл бұрын
@@chandoo_ basically vlookup match combo gives you the data from multiple columns by matching the headers. This is basically a smart replacement of the column number argument in vlookup. What is the equivalent of that with xlookup.
@stayupdated3893 2 жыл бұрын
Sir please make a video on Roadmap for Data Analytics
@chandoo_ 2 жыл бұрын
This is already in the pipeline. Stay tuned.
@ursvenky6394 2 жыл бұрын
Wow chandu
@chandoo_ 2 жыл бұрын
Thanks Venky...
@KiranKancharla 2 жыл бұрын
Asking out of curiosity, just adding an extra column by concatenating, isn't easing the process?
@chandoo_ 2 жыл бұрын
How would you know which value to lookup then? You still need to know the latest price update date to solve the problem.
@KiranKancharla 2 жыл бұрын
@@chandoo_ indeed, after finding out the maxif. Yes.
@user-fq4xi7yj3k 2 жыл бұрын
he looks like mark spector in moonknight
@chandoo_ 2 жыл бұрын
I can't tell the difference between bedsheets and spreadsheets!!!
@user-fq4xi7yj3k 2 жыл бұрын
@@chandoo_ helloo moonknight
@user-fq4xi7yj3k 2 жыл бұрын
kzfaq.info/get/bejne/rp17pc-SxLvcgps.html
@sridharramadurai1206 2 жыл бұрын
Does this work ??? I don't this so... I have tried.. It doesn't. Some steps are missing I suppose......
@sridharramadurai1206 2 жыл бұрын
My apologies..... My data had spaces in the beginning. Now it's working fine. Excellent Idea. Excellent feature.
@chandoo_ 2 жыл бұрын
Glad it all worked out :)
@kraanthi19841 2 жыл бұрын
Could you please post us the solutions without using XLOOKUP as many might not have Microsoft 365 version or latest version of excel
@kraanthi19841 2 жыл бұрын
Many thanks for the tip.
@chandoo_ 2 жыл бұрын
In such case, you can use either sumproduct or sumifs. See this article I wrote several years ago - chandoo.org/wp/multi-condition-lookup/
@kraanthi19841 2 жыл бұрын
@@chandoo_ Many Thanks
@Soulkeeper115 2 жыл бұрын
Pivot table solve all issues lol
@chandoo_ 2 жыл бұрын
Hmm.. care to elaborate? How would a pivot solve this particular issue?
@xubyrkhan 2 жыл бұрын
Boothi seedhi kr lei bhai, xlookup sahi hojai ga
@chandoo_ 2 жыл бұрын
What does it mean?
@jaydingiesler5280 2 жыл бұрын
See, this is what I don’t like about Excel. That should be such an easy request and it got super complicated very quickly. Have I been ruined by Power Bi reports? 😂
@chandoo_ 2 жыл бұрын
kind of. But also, if you have same data in Power BI, I am not sure you will get the result with simple DAX either.
@supersmart671 Жыл бұрын
My INDEX MATCH and XLOOKUP are give two results. I would trust INDEX MATCH rather than XLOOKUP
BalcevMMA_BOXING
Рет қаралды 14 МЛН
Satish Dhawale English
Рет қаралды 1,7 М.
Sameer Gaming
Рет қаралды 2 МЛН