Try The DGET Function Instead of INDEXMATCH & XLOOKUP

[@KenjiExplains]

👉 Take our Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance

[@Hamzazubair2014]

Ok

[@ianpaulkenchington4311]

Hi Kenji! There is also an easy way of dragging an xlookup function in your example: XLOOKUP(; ; Table[range]) can be changed to: XLOOKUP(; ; Table["&&"]) That makes XLOOKUP more dynamic

[@natecarel5949]

Thanks for including the section on limitations. It was actually very helpful in understanding the formula itself

[@KenjiExplains]

Glad it was helpful!

[@ritadwi-m5q]

THIS CHANNEL SO MUCH HELP, THANK YOUU SO MUCHHH

[@Jennakp7]

Needed this a lot, saved me at work, but my PC isn't working very well, I might need to get a new PC with or a good Office... driving me CRAZY

[@joshuaLLM1]

Girl, there is a good one called BNH Software, but you might need to fix that PC, probably other thing, don't go "CRAZY" ;)

[@keithM7]

I got mine from them as well, for PC perhaps a Ryzen 5 with good RAM, Office needs a good PC, especially for work

[@AbdulRahman-yp6oy]

Excellent Kenji , easy explanation , also you teach limitations of formula Thanks a lot

[@KenjiExplains]

Thank you 🙏

[@faizujutt4942]

Sir, please make a video on Power Pivot, Data Modeling, DAX, & Relationships.

[@AbdulRahman-yp6oy]

Yeah yeah 👍

[@ambrose_vevo137]

Your the Best,Kenji i just try to listen to others ,how they teach on their chanells ....Your a Good teacher...😊

[@KenjiExplains]

I appreciate that!

[@driekeijlders281]

Nice videos Trick for you: try using the arrows when you hover the header or first column lines: one click for data next click for header included.

[@auliakhoirunnisa9447]

Thank you so much, Kenji! You really make everything related to Excel easier! Been binge-watching all of your videos. Once again, thank you so much! Keep up good work!

[@KenjiExplains]

Thank you!

[@flujensiosserwanji6624]

Wow this really great. Never heard of DGET before. I was curious seeing the notification. Thank you for the video❤.

[@KenjiExplains]

Glad it was helpful!

[@sebastiencrepel5032]

Very well explained and very useful. Thanks

[@malkogindrat]

12:04 is the big killer for DGET for most of the situations where you would use XLOOKUP and/or INDEX/MATCH. DGET is for managers...not for data cleaning, mining and stats. The one line example is designed to advantage DGET.

[@GurpsB]

brilliant video once again! will be purchasing your business guide for sure thanks

[@KenjiExplains]

Awesome, thank you! Liverpool fan I see you XD

[@TheNancystella]

Very useful. Thanks Kenji 😊

[@GergelyCsermely]

Thank You! Excellent content!!!

[@camlex6310]

Really interesting uses!!

[@markpodesta4605]

Thank you Kenji. Didn't know about DGET.

[@KenjiExplains]

Thanks for watching :)

[@dunghuynhchi8229]

Thank you for your video, it was very helpful

[@AbdulRahman-yp6oy]

Very Thankfull to you ❤ Excellent Please make videos on Power BI and Power Query

[@kleopatrabecir-fornalik6058]

Great video Kenji

[@lakunagr]

great video didnt know about DGET thanks

[@fernandosousa5480]

Great video ❤

[@jazzista1967]

Joder Kenji.. que buen truco esa funcion de DGET... La voy a comenzar a usar de nuevo.. Gracias

[@KenjiExplains]

Muchas gracias! Un saludo 👍

[@nkechiizeji]

Please upload part 2 and the remaining parts❤

[@howietjai]

7:46 Those driver names 😂😂

[@KenjiExplains]

haha glad you noticed XD

[@willzinner8813]

been using excel for a while but first time i hear this formula

[@KenjiExplains]

it's a nice alternative!

[@SEYHAR-px9fb]

Please make a video about finding names and gender by showing resume. Thank you!

[@Gorman-84]

This is cool. Thanks

[@williamarthur4801]

Had forgotten all about Dget and did not realise it was dynamic which is one of the drawbacks the others.

[@KenjiExplains]

Yes!

[@igormajrov8444]

If a column has more than one matching value, DGET will generate an error. VLOOKUP will produce the first match.

[@stanTrX]

Thnkx does it also find fuzzy lookup?

[@masoodnkhan1]

thks, it helped!

[@jack.ying28]

Hi Kenji, firstly thank you very much for the clear walkthrough. It’s helpful. I would like to enquire if there’s any function that DGET work with, such as the same from XLOOKUP with multiple criteria? Thank you!

[@ianpaulkenchington4311]

Hi Jack, there is indeed a way. XLOOKUP makes a set of TRUE and FALSE statements in the range where it looks for the value, so you can do it one step ahead: if you have a range1 and a range2, with criteria1 and criteria2, you would write it like this: XLOOKUP(1; (range1 = criteria1)*(range2 = criteria2); ) Whenever there is a match in both criteria, TRUE times TRUE equals 1, so the XLOOKUP will find a match where there is the double match. Nevertheless, DGET has a much intuitive way of looking with multiple criteria though, by changing the size of the last parameter. Does that help?

[@datanalyst]

Thank you!

[@amarmanhas8432]

Are there any offers for Black Friday sale as it is expensive right now

[@jessicabarroga1152]

thank you so much for sharing! this will definitely make my formulas so handy! quick question, say I bought the course for ‘Excel for Business and Finance’ months back, do you update these whenever you discover new techniques and will I have accessibility to it?

[@KenjiExplains]

Thank you! Yes the course gets updated often (last update was last month to add new Excel features and improve lesson delivery) you can reach out to [e-mail] if you have any other doubts!

[@calgrizzlybear2982]

What does the DGET return when there are multiple Esteban or Carlos for first name?

[@ashokbezawada965]

COULD YOU PLEASE LOWER THE PRICE FOR EXCEL AND POWER BI

[@oreofeir6493]

Hello sir, I am looking for a lookup function that can look through a table of multiple columns and return data in the last column when data on any of the 1st, 2nd, or 4rd column is looked up

[@santhoshs6987]

Can we use dget to pull information from different file

[@Glass_Half_Full]

in 4:06, what happens if you typed "Spain"? In my case, when I choose the first name on the table excel returns a #NUM error.

[@IAmInevitable1337]

i got 3 google docs i try connect em all together but on description it should only follow part of it and ignore rest/filter and on price it should get all any help how can work

[@BT_Gaming_Clips]

Kenji, for the DGET vs XLOOKUP, could you not do a nested XLOOKUP where return value is a second criterion to filter for the month? Although you can do this in XLOOKUP, your logic still stands that DGET is simpler, just clarifying that you could have done that in the example you provided.

[@KenjiExplains]

I'd have to check but I think what you propose would work! As you mention, the DGET might be easier to do (and for others to read/understand) vs a nested XLOOKUP

[@extraktAI]

Lovely!

[@KenjiExplains]

Thank you!

[@ulaganath]

how abt mutiple values match

[@bhavneshparikh8283]

Can you share pdf of 101 ways to master in pivot tables

[@matsoj63]

POLAND MENTIONED!!!!

[@KenjiExplains]

Let’s go! Haha

[@telo7979]

Is DGET any better peformance wise? My XLOOKUPs are struggling with the amount of data i am analysing

[@volvo945]

100% agree. I have some XLOOKUP formulas taking 10+ minutes to calculate.

[@KenjiExplains]

Interesting, I haven’t tested that but could be worth a shot!

[@AP-eb8hd]

@@volvo945 time to change your infra.

[@toddbhalford]

@@volvo945 XLOOKUP is a natural array formula, so if you have multiple criteria it has to iterate through EVERY permutation for EACH ROW which is computationally intensive. A good alternative (if you do indeed need multiple criteria) is to create a concatenated column in both your destination and lookup tables and XLOOKUP on those. It's obviously not going to work for every scenario, but I've found it to be significantly more performant. If not, use power query!

[@branbroken]

Interesting to test, though id imagine since its parsing the entire table rather than just the relevant columns, it would be slower than xlookup for larger datasets.

[@volvo945]

I’m going to try DGET to replace some of my xlookup functions. XLOOKUP is a core hog if you’re doing AND type logic on large tables.

[@planilhador]

After the Filter function, I stop to use the Vlookup. With these limitations, It appears that Dget also won't affect my Filter use.

[@kunakunachannel3346]

My idol😊

[@KenjiExplains]

Thanks for watching :)

[@hassaanaasim550]

Which Excel is it? 2019 or later?

[@kurienillirickal7195]

xlookup is my favourite. Now dget to be checked

[@ratulmitra347]

if i put in "dget wildcard feature" the surname then dget doesn't work....

[@forktrader7870]

alternative filter formula?

[@UNKNOWN-r3u5k]

Hey in my excel when I do the same with excel data it showing me number errors and - #NUM like this

[@Shuvoorahman]

Awesome

[@НаталіяК-г8г]

Can you advise the formula for dates? Let's imagine the case when the car X was provide with a pass Y for some period (the pass was issued on May 4, 2023 to June 4, 2023). But I know that the same car was visiting some place on June 7, 2023, i. e. without any pass. How do I check it by formula? Thanks

[@KenjiExplains]

hey i made a whole video on date functions here: kzbin.info/www/bejne/b3e3moetiahpepY

[@Masonda]

Hi Kenji, I was just working on a project that basically requires me to do this, but the other way around. For example; I have a number of 2,036, then my output should be Switzerland. This required me to make a string with =vlookup and string it together by using (if not found ~> vlookup).. it’s a large document, so I strung it together 50 times.. Making my file extremely slow. Any tips?

[@ratulmitra347]

Brilliant but i can't use this as my excel is 2016.... But thanks anyway...

[@RAHUL-ms1on]

it's no DGETt if can not drag

[@livinginvancouverbc2247]

Excel Warriors... "Can you DGET?" "Can you DGET?" super deep voice..."Can you DGET?"

[@TalShaba]

About the last limit... You can write a dynamic formula with Idirect&Address&Match. then... You can make it dynamic But I found all those "D-functions" useless. Every action there's a simpler way...

[@utkarshsinha3277]

🎉

[@KenjiExplains]

Thank you!

[@Hamzazubair2014]

Huh?

[@SheldonCooper-tc8zr]

Limitations are soo big 😂

[@GTLBikes]

Discount on course Excel and Power BI

[@TrevMA]

Ehh. Having to organize the criteria that way makes the DGET function wayy more trouble than it's worth to me. Would rather just use custom lambdas wrapping over XMATCH/XLOOKUP/FILTER functions as needed.

[@alexcham9747]

So… A dget function is like a glorified filter? 😂

[@Hamzazubair2014]

First

[@KenjiExplains]

Nice!

[@KE-cf5je]

that is a terrible function. in most use cases youd want to apply this formula to an entire collumn, which is not possible with dget. Indexmatch still remain king.