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