Where INDEX-MATCH Wins Over XLOOKUP and VLOOKUP

OFFSET is also an option because XLOOKUP returns a range. =OFFSET(XLOOKUP(),1,0) but keep in mind OFFSET is volatile, so you may want to avoid in a huge workbook.

Like all problems, there are normally 6 ways of getting the same result. Really like XLOOKUP and as long as you don't really need 1.04M+ rows =XLOOKUP(E3:E9,B3:B500000,B5:B500002,"Name Not Found") better yet if the name list is a spilled array, then = XLOOKUP(E3#,B3:B500000,B5:B500002,"Name Not Found") -- make your last row bigger if needed. ---- sorry didn't realize this video was 3 years old.

Thank you very much.

I just know how IndexMatch is really good today.

I love it, no toying around and trying to sound cute. Just straight to the point. Please show everyone else how to keep it simple. Thank you for sharing!

Grrrrrrrrr.

I was today years old when I learned of a good and valid reason to use IM over x or v

The key to all concepts is a realistic use case. You nailed it Oz

( ps. This is Tom hinkle )

superb, thanks Oz

Xlookup

Thats leGIT!!

😂😂😂 I like this ohhhhh

I’ve been preaching index/match for years, and still use it for its performance advantage, but there’s a way with xlookup, and it’s NOT offset () …volatile… ugh! Xlookup doesn’t care whether the return array and the lookup array start on the same row. You’ll have to select ranges and not the whole column, but just start the lookup range on the row with the name and the return array on the row with the email and voila!

Heard you on Risk👍🏾

Instantly subscribed. Ur my teacher.

Man , I like the way you deliver the informations. You have made a difference and create value to Excel community... build up the momentum 😉.

Wow

great video Oz. thanks. I have included it in today Excel Curation corner, and I have sent you on some $bat and $hive tips

How can use multiple match condition in index and match formula

Nice and tight. 👍
Xlookup also can do offset Plus build-in IFNA
=XLOOKUP(E3:E9,B1:B1001,B3:B1003,"")

MATCH is not needed anymore. INDEX & XMATCH will have you more than covered.

Hi Oz, I really enjoy learning with you! Simple, logical and easy. After each video I have a smile on my face for a long time. It is really fun the way you teach. Thank you!

Awsome Oz Ji... sweet and simple solution
we try wrapping with the filter the function in XLOOKUP...
=FILTER(XLOOKUP(E3:E10,B3:B10,B5:B12,""),XLOOKUP(E3:E10,B3:B10,B5:B12,"")<>0,"")

As Always...👍

right video in right time.:). Thanks for sharing Index Match importance.

Wow! That's amazing. You came up with the solution. Yes, maybe everyone try to find way using vlookup or xlookup but like you said, right tool for the right situation. Thumb up!

I am so used to Index Match, that I just type them automatically forgetting that XLookup exist, althought I am trying to use XLookup too

Genius 💥

❤️

Well Done

Brilliant solution. I like how concise it is - easy to read - not overly complicated. Solves the problem. Beautiful

Oz, you are amazing! Thanks for teaching.

👾👾👾🔥🔥🔥🔥🙌🙌🙌🙌

I get it: if you want to offset the index, this is PERFECT! Good call — awesome!!
BTW: since it is NOT volatile, it beats OFFSET (the function) any day of the week!
Oh, and it is simple, too. :-)

Hi Oz, cool challenge. I did solve it with XLOOKKUP in this way: =IF(XLOOKUP(E3:E10000,B3:B10000,B5:B10002,"")=0,"",XLOOKUP(E3:E10000,B3:B10000,B5:B10002,"")). The trick is to offset the return_array argument by +2 rows vs. the lookup_array argument. Designed in this way, the single formula will spill the email addresses for any matching names that you type in column F. If no match, then you get a blank (i.e. ""). The above assumes 10,000 rows is sufficient for your data and or desired name matches. I love INDEX / MATCH, but this is one way to get a spilled, dynamic result using XLOOKUP which might be useful if you add or remove the search names regularly. Fun to solve things in different ways. Thanks and thumbs up!!

Oz a supporter of INDEX-MATCH and a denier of XLOOKUP. People with years of Excel experience have a wistfulness for old functions. It's right? Great explanation!!!

So simple and short video, Oz, but the content is so important. You may know a whole lot of tools, but to choose the right tool is a totally different skill! Thanks Oz

Oz, as always brilliant! There are a lot of situations in real life where the reaction to "let's automate and streamline this process!" is always "NOPE"... That is when the real crafter of Excel needs to look at the toolbox and choose accordingly! Long live INDEX MATCH!!!

if you were my teacher, I probably would have learned a lot more in college.

That looks like the cousin to my OFFSET-MATCH :)