XLOOKUP is awesome. I learn something new in each of your videos. Using the CHOOSE function as well as returning custom values if not found was mind blowing for me. Great video. 2 Thumbs up.
Nice, thanks! It was nice to see how to get any number of columns and they don't have to be side by side. Please always upload a Sample file so that we can practice with you.Thanks
Hello, nice video thank you, a question how do you search if the columns are not next to each other, VB the first product is in column B with 20 rows below it, the 2nd in column F the 3rd in J how do I proceed?
Is there a way to leverage this function to return multiple matches? I have a file I need to search that has a different number of matches. To use your example - if Brand was in the item I was looking to match size and price against. Is that possible?
if we would like to change sequence of header in choose function by selecting data validation list, what do we need to change? Because you wrote hard code in Choose function ({1,2,3}) and it can’t be changed by selecting different options.
Hallo sir Can you please make video on this topic : How to setup page like A3 ,A4 and make pdf of a particular page Using app script & Also set-up margin and page break up using app script . Please help me 🙏 In Google sheets only
I tried it today. It works if your data is not in a table but if it is in a table then CHOOSE Function didn't work for me. Have you tried it with tables? Thanks
@@ExcelGoogleSheets I tried it and here is the formula but it didn't work. =XLOOKUP([@Name], Table2[Name], HSTACK(Table2[Phone], Table2[ID], Table2[Company])) but thank you for the CHOOSE Formula that works perfectly with range but not with table.
@@ExcelGoogleSheets It gave me an error #SPILL!. I had pasted my formula earlier. Looks like it didn't paste. =XLOOKUP([@Name], Table2[Name], HSTACK(Table2[Phone], Table2[ID], Table2[Company]))
How can we print the names of the combined workbooks in your "ua-cam.com/video/x2Cr9hi3MnY/v-deo.html" video in the first or last column? When too many books are combined, it cannot be understood which data is taken from which workbook. Thank you very much in advance for your help.
Hi, I asked this question under your other video. Will you help or do you just read and pass the comments? I enjoy watching your videos and I look forward to your new videos. I hope you can help me with this. (I have 450 workbooks. I want to combine them and use them as database, but I need to print the workbook name in an empty column in the merged workbook. Otherwise, merging the workbooks does not make any sense. I hope you will write an answer that will solve my comment.)
@@ExcelGoogleSheets Hi, First of all, thank you very much for your reply. I've watched your videos many times, but I couldn't quite understand how to do it. I would be very grateful if you could write more clearly what changes or additions I should make on which line.
Excel Advanced VLOOKUPS ua-cam.com/video/XOSfC22VxyA/v-deo.html
Excel Advanced Formula Tricks ua-cam.com/video/kf8vaA5Utt4/v-deo.html
Excel Find Last Non-Blank Cell Value, Row or Column ua-cam.com/video/sEev0esY-ik/v-deo.html
how to make xlookup sort results in column?
My problem the zero not change😢
XLOOKUP is awesome. I learn something new in each of your videos. Using the CHOOSE function as well as returning custom values if not found was mind blowing for me.
Great video. 2 Thumbs up.
For me too! Game changer!!!
Excellent! Simple video with lot of information! 😊
You have earned a new subscriber today.
Nice, thanks! It was nice to see how to get any number of columns and they don't have to be side by side. Please always upload a Sample file so that we can practice with you.Thanks
This is gold! Amazing!
I’d love to see this for Google Sheets.
=Query() is what you're probably looking for
Awesome tutorial, thanks a lot
:)
Sir using xlookup for array it is returning #VALUE! error, while normal return for single column output but fails for multiple returns.. plz help
Hello, nice video thank you, a question how do you search if the columns are not next to each other, VB the first product is in column B with 20 rows below it, the 2nd in column F the 3rd in J how do I proceed?
Thanks a lot 😊
Very cool! Can you get a spilled array result from XLOOKUP, if you give it an array as the first argument? Then one cell would have everything!
Yes.
Important - can we use two lookup values instead of one?
Maybe you can only select multiple columns on the paid version of excel? This isn't working on the online version. I get value error when i try this.
Hello, why when i try this formula xlookup with choose, my result is spilling down not right ? I have microsoft 365.
if my lookup value in three different column and i want to return from only one column how to use xlookup than ?
Is there a way to leverage this function to return multiple matches? I have a file I need to search that has a different number of matches. To use your example - if Brand was in the item I was looking to match size and price against. Is that possible?
No. Use FILTER function instead ua-cam.com/video/_qEc8CEgUl4/v-deo.html
Actually, this is probably what you need instead ua-cam.com/video/q4kYG1RbTic/v-deo.html
@@ExcelGoogleSheets Thanks! I'll check them out!
Awesome
when i try to do this with arrayformula, it would only return the 2nd column. is there any way I can work around this?
What's your formula?
superb
I can't press F4 on my laptop , any idea how to fix ,because when I press it close all windows like an exit
if we would like to change sequence of header in choose function by selecting data validation list, what do we need to change? Because you wrote hard code in Choose function ({1,2,3}) and it can’t be changed by selecting different options.
Depends on your setup. Not exactly sure where you validation is and what it contains.
@@ExcelGoogleSheets it contains your table header
I'd suggest using INDEX->MATCH if you want to use headers. It will be much easier than XLOOKUP.
Here is an example how ua-cam.com/video/2T__Yyn_Bdw/v-deo.html
Hallo sir
Can you please make video on this topic :
How to setup page like A3 ,A4 and make pdf of a particular page
Using app script &
Also set-up margin and page break up
using app script .
Please help me 🙏
In Google sheets only
What if I want to remove the spaces that have a blank or 0 return?
What you mean by "remove the spaces"
I have tried to use this formula but my is getting to heavy to respond can you some other method
Hi, please when I want to copy the result as value(D2:D10) the result in E2:E10) Disapears, please help
I follow your directions exactly and receive #spill error even though there are duplicate lookup values
I have the same problem. Did you figure it out?
I tried it today. It works if your data is not in a table but if it is in a table then CHOOSE Function didn't work for me. Have you tried it with tables? Thanks
Use HSTACK(Table[col1],Table[col2],Table[col3])
@@ExcelGoogleSheets Thank you! I will try and let you know.
@@ExcelGoogleSheets I tried it and here is the formula but it didn't work. =XLOOKUP([@Name], Table2[Name], HSTACK(Table2[Phone], Table2[ID], Table2[Company])) but thank you for the CHOOSE Formula that works perfectly with range but not with table.
What error did you get with HSTACK?
@@ExcelGoogleSheets It gave me an error #SPILL!. I had pasted my formula earlier. Looks like it didn't paste. =XLOOKUP([@Name], Table2[Name], HSTACK(Table2[Phone], Table2[ID], Table2[Company]))
how to make xlookup sort results in column?
Sort which results?
Is there any XLOOKUP in Google sheet? If yes then plzz make a tutorial about that..
VLOOKUP is XLOOKUP for Google Sheets.
why he's pressing f4? could someone explain it?
Watch this ua-cam.com/video/mTWh3VP6HPk/v-deo.html
When i choose multiple columns as the return array, i get "#Value!" Error . Can someone help me?
I'm having the same problem. When I select only one column it's fine.
How can we print the names of the combined workbooks in your "ua-cam.com/video/x2Cr9hi3MnY/v-deo.html" video in the first or last column? When too many books are combined, it cannot be understood which data is taken from which workbook. Thank you very much in advance for your help.
Hi,
I asked this question under your other video. Will you help or do you just read and pass the comments?
I enjoy watching your videos and I look forward to your new videos. I hope you can help me with this.
(I have 450 workbooks. I want to combine them and use them as database, but I need to print the workbook name in an empty column in the merged workbook. Otherwise, merging the workbooks does not make any sense. I hope you will write an answer that will solve my comment.)
File object has getName() and getUrl() methods you can use to get the name or url of the spreadsheet.
@@ExcelGoogleSheets
Hi,
First of all, thank you very much for your reply.
I've watched your videos many times, but I couldn't quite understand how to do it. I would be very grateful if you could write more clearly what changes or additions I should make on which line.
Waiting for that formula on Google Spreadsheetq
Use QUERY function