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
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?
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.
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.
Watch this, it covers pretty much everything you need to know. ruclips.net/video/uuei1LzZcw0/видео.html For something more interesting you can watch LAMBDA Array functions videos ruclips.net/user/LearnGoogleSpreadsheetssearch?query=lambda%20array I've recorded these months ago for Excel, now they all work in Google Sheets as well.
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!
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.
Exactly what I needed
you have a really superb style of presentation - thank you very much 🙏
Thank you!
Great,easy to understand
Thanks, that was well presented and very easy to follow.
Thank You!
Good to hear about the availability of xlookup in Google sheets.
Still need to wait for it appears in my sheets.
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 :)
Thanks a lot.. i was waiting for this video..
👍
Thanks again for the great content!
👍
Great vid! Really appreciate it
👍
excellent video, also appreciate there's no background music so I can add my own :)
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. ruclips.net/video/0yMOMSBENbo/видео.html
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
👍
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 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.
Thank you so much❤
👍
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.
Great
Sir pl, please suggest which Antivirus is best for Laptops
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.
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.
Do you have any patreon link where we can download your scripts?
www.patreon.com/chicagocomputerclasses
How about multiple matches? How to get the sum amount?
use SUMIFS function
Can I have practise file? Thank you.
docs.google.com/spreadsheets/d/1M-eL4Lj62ZrhJKM6x1FugWptwzB3dJLtk0orEFQobsA/edit?usp=sharing
Make a video on Named Function. Pls..
Watch this, it covers pretty much everything you need to know. ruclips.net/video/uuei1LzZcw0/видео.html
For something more interesting you can watch LAMBDA Array functions videos ruclips.net/user/LearnGoogleSpreadsheetssearch?query=lambda%20array
I've recorded these months ago for Excel, now they all work in Google Sheets as well.
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. :)
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?
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"))