XLOOKUP v3.0; Power Query Import from Web; Intellisense, Text.Length

We put a lot together in this superbly delicious Excel tutorial. We start with the revised XLOOKUP version 3.0 which has the "if not found" component moved to the 4th position instead of the 6th.

Then we move to import data from a webpage and clean the data using Power Query's FILL Down, Text.Length and other feature. Then, the final component in this deliciousness is a Left Outer Join. It's all here for your delight.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:

My book: Guerrilla Data Analysis 2nd Edition

#XLOOKUP
#PowerQuery
#MSExcel

[abdelrahmanabdou]

Great dish by our Excel Master Chef OZ as usual :)

[cassiusclaudius]

Your videos are so entertaining I am binge watching them for fun, not because I need help right now. And I have learned more in 24 hours than I have from any textbooks in the past 25 years! Thanks for the help, and kudos to you for your video production wizardry!!!!

[barttitulaerexcelbart]

With so may ingredients, it can only be tasty. Thank you Oz. !

[tgasperin]

Excellent! Thanks for the tutorial. This actually gave me an idea to solve a different problem. Man, I was struggling to solve that.Tks a lot!!!

[wayneedmondson]

Hi Oz.. cool challenge and solutions. What's for dessert.. haha!! Thanks for all the great videos.. good learning.. entertainment.. humor.. always a pleasure :-)) Thumbs up!

[excelisfun]

Thank you for the Delicious Excel Fun, Oz!!!!

[Computergaga]

Oooh! That was tasty 😅 XLOOKUP and Merge Queries in one video. I am full.

[heikoheimrath]

great stuff as usual - have a great weekend

[jasonm]

Nicely done.


I was thinking about the search mode (last-to-first) argument in XLOOKUP.
=XLOOKUP(B4, Table1[Person], Table1[Arrival], "no travel", 0, -1)
It could go bad if some joker comes along and sorts that source table however they want, instead of by date.


If you want the last arrival regardless of table sort order, you could add a pinch of SORTBY to your tasty XLOOKUP recipe:


=XLOOKUP(B4, SORTBY(Table1[Person], Table1[Date]), SORTBY(Table1[Arrival], Table1[Date]), "no travel", 0, -1)

[Exovideo-LeFormateurDeGestion]

Waouh !!! Very usefull ! Thank you Excel On Fire

[xtnctr]

"Excel on Fire tutorials are getting hotter than my whole filmography combined." (Jameson, Jenna. 2019).

[jawadmoktadir]

You’re a Magician 🎩 🎩 🎉 !!! And that was so Yummy!! Thanks, Sir!!! So useful bravo 👍

[ExcelExciting]

That is amazing recipes... just love it 😍

[rebi]

Kralsın adamım :) (You are king my man :))

[Plutarkthefirst]

Great work as usual, I wish all my trainers were engaging like you!

[hashi]

Fantastic as usual! I'm seriously going to lose my shit if Microsoft keeps this in Beta for the next year like they've done with Dynamic Arrays

[wesszep]

'Del'intellicious' thanks Oz

[babarqureshi]

Marvelous work 👌. Sir how can I activate xlookup in Excel 2013.

[mazharhussain]

no doubt you have good understanding but your way of teaching iS NOT up to the mark. YOU TALK TO