XLOOKUP - Excel & Google Sheets, XLOOKUP function from Other Worksheets, Join/Merge Data
Вставка
- Опубліковано 4 лип 2024
- XLOOKUP - Excel & Google Sheets, XLOOKUP function from Other Worksheets, Join/Merge Data.
00:00 XLOOKUP - Excel & Google Sheets
01:59 XLOOKUP Exact Match
08:17 XLOOKUP - How to Return Multiple Columns
10:36 XLOOKUP Type Match, How to Fix Errors with Number Lookups
14:26 Join Data/Tables using XLOOKUP
20:49 XLOOKUP from Another Worksheet (Tab)
22:56 XLOOKUP Array Formula in Excel & Google Sheets
25:21 Handle N/A, Custom "If Not Found" Values in XLOOKUP
28:05 XLOOKUP Next Smaller or Larger Item (Similar to Approximate Match VLOOKUP)
35:28 XLOOKUP Wildcard Character Match
42:27 XLOOKUP - Find Last Match
45:29 XLOOKUP Binary Search
51:03 XLOOKUP Horizontal Search
53:20 XLOOKUP Horizontal & Vertical Array Mix
#xlookup #excel #googlesheets
you have a really superb style of presentation - thank you very much 🙏
Thank you!
Good to hear about the availability of xlookup in Google sheets.
Still need to wait for it appears in my sheets.
Thanks, that was well presented and very easy to follow.
Thank You!
Excellent video, learned more in one video than a hundred others, especially using x look up when searching data fRom two separate tables and sheets, as well as array return ranges, and all of the other item. Perhaps you can show how to use x look up in place of index match
I've been waiting on this day since forever!!! God I'm so happy
At least someone is :)
Quality work and easy to follow. 👍🏻 Can xlookup return values from a range of Sheets? If so, is this something you’ve covered already? Thanks
Thanks again for the great content!
👍
Thanks a lot.. i was waiting for this video..
👍
excellent video, also appreciate there's no background music so I can add my own :)
Great vid! Really appreciate it
👍
Great,easy to understand
Thank you so much❤
👍
Awesome! I’m assuming the look up can be a date as well, will try it out.
Yes, can be dates.
IT helpful, thank you
👍
Thanks for an excellent video.
Now that xlookup is supported in google sheet is there any advantage using vlookup? Or from now I should use xlookup only?
Well, it's a difficult question to answer. VLOOKUP could be better if you want to work with one range instead of 2 ranges. You might also want to make sure your spreadsheets are compatible with older Excel versions in certain cases if you're emailing files to clients.
Since you're asking this questions, I would say just use xlookup.
Good vidéo as usual… I have a question: is it possible with xlookup to return multiple columns when you add an arrayformula ?
No, it doesn't work. Use VLOOKUP instead.
Sir pl, please suggest which Antivirus is best for Laptops
Thank you for show difference between excel and Google sheet
👍
This is great. I cannot get this to work in Google Sheets using 2 different sheets. Your example only shows using tabs within the same sheet. Do you know how to get it to work with 2 sheets?
Sure. Use IMPORTRANGE function. ua-cam.com/video/0yMOMSBENbo/v-deo.html
Do you have any patreon link where we can download your scripts?
www.patreon.com/chicagocomputerclasses
permission to ask
about wildcards in spreadsheets, if the search key is a serial number instead of text, can this be done?
then alternative code "*"&..&"*" ?
You can add wildcards to number, which will basically convert it to a string.
The important part is what's in your actual data where you search this? Is it text or number type. If it's number type you'll need to convert it to text.
What is the keyboard shortcut on a Macbook for absolute cell reference? You said it in one video and I forgot it. But I don't know which video it is. I can't watch all of them again. You're the only one that knows this shortcut. Please help me.
same, F4
You usually need to press "fn" key to access it.
Make a video on Named Function. Pls..
Watch this, it covers pretty much everything you need to know. ua-cam.com/video/uuei1LzZcw0/v-deo.html
For something more interesting you can watch LAMBDA Array functions videos ua-cam.com/users/LearnGoogleSpreadsheetssearch?query=lambda%20array
I've recorded these months ago for Excel, now they all work in Google Sheets as well.
Can I have practise file? Thank you.
docs.google.com/spreadsheets/d/1M-eL4Lj62ZrhJKM6x1FugWptwzB3dJLtk0orEFQobsA/edit?usp=sharing
Actually you don't need an array formula if you want to set a range as your search key with XLOOKUP in Google Sheets if you lock the range with F4. Anyhow, great job as usual, thank you!
Really? That seems counter intuitive. Are you sure it's not auto filling the formula automatically?
How about multiple matches? How to get the sum amount?
use SUMIFS function
You didn't mention one of the greatest features of xlookup: lookup array can be "after" the result array.
Yes, I did. You should watch the video again.
I'm not sure about "greatest" though. :)
11:26 try value() around text in the formula. Then you don't need to convert
It's probably best to covert in case you have some letters someplace as well, because value will throw an error. But if you only have numbers it should work.
I have a vlookup formular, how do I convert to XLOOKUP? | =VLOOKUP("MODEL 1",{IMPORTRANGE($B2,"DCF!D9:N13")},10,false) | thanks!
=XLOOKUP("MODEL 1",IMPORTRANGE($B2,"DCF!D9:D13"),IMPORTRANGE($B2,"DCF!H9:H13"))