Better than XLOOKUP? Try SUPERXLOOKUP!

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★
Sign up for our free Insiders program and get the example file:
File Reference for this video: 0237 SUPERXLOOKUP.zip

★ About this video ★
XLOOKUP doesn't spill vertically and horizontally at the same time. So why don't we build a custom function that does. It will do everything XLOOKUP can do, but more.

0:00 Introduction
0:31 What is SUPERXLOOKUP?
1:10 The problem
2:15 Handling errors
2:58 If not found
3:28 Horizontal fails
4:15 Adding variables
6:05 Horizontal lookup
7:30 Custom function
9:32 Testing
10:48 Using the function in other workbooks
11:15 Wrap-up

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[vida]

What a creative name - SUPERXLOOKUP and great functionality! I think Microsoft should buy this function from you!

[IvanCortinas_ES]

Tons of talent, Mark. Excellent resolution, which also controls errors. Awesome. Thanks for sharing.

[Martinrd]

Just sent the link to this video to my work email. Will be trying this out tomorrow. Great work, exceptional!

[balintbatonyi]

Hello Mark, thank you for the inspiration to think further factory functions. Also mentioning Sandor Debreceni due to our meaningful conversations. My version 1) needs no table format source, 2) only takes one argument, which is the full source range and 3) shows missing lookup values with two different error handling icons (missing row lookup 🔙 and missing column lookup 🔝). For adding as own formula, paste this: FirstRow;ADDRESS(r;c-1;4;1); FirstCol;ADDRESS(r-1;c;4;1);

[tgorman]

Recently discovered how Lambda functions work, and this is a great example, did not know that copying and pasting the formula would carry the formula over, great tip!

[weslleyhenrique]

Incredible!

I love videos that use formulas.

[Starlit]

Wow, awesome!! Thank you so much Mark for sharing. Will try this out... Just right timing to get me unstuck 😊.

[ziggle]

I have immediate use for this function. Thanks a bunch!

[franzzzis]

incredible.. very impressive, thank you for your work

[deepakmirchandani]

great job, sir, thanks for such an excellent formula.

[stevereed]

Outstanding Mark, what a great solution. Thank you

[waitplanwp]

Mark, you are an angel, you have helped me so much in my professional life and thanks to you I have progressed in the workplace.
God bless you, thank you always for all the help, amazing man and so much fun that you share your genius with us!

[martyc]

Nice one Mark- this really would be a great built in function.

[thebesttechnical]

Thank you so much for this. I have this urge to learn more about Excel!

[umarhassan]

Can't wait for your next formula. Superb! Thanks a lot.

[silvanodemetrio]

As usual, you are always EXCELLing yourself

[teoxengineer]

So EXCELlent presentation....It contains everything (XLOOKUP function evaluation from excel team 😉)

[MounaSammoud-td]

this is awesome thanks for sharing Mark 😊☘

[Quidisi]

Can't wait for the SuperDuperXLOOKUP video coming out where Mark builds a formula that spills not only on the X and Y axis, but also the Z axis! 😁

[shriji-g]

Mark,
This is awesome ❤
In your next video, please do it using Power Query.
Thanks in advance 😅