Hi Alan! Thank you for the trick. Your example shows a table format which is fine but for whoever is using a normal excel format (no table format), you can also use the same trick but the reference of the COLUMN () formula needs to be locked with absolute reference on the rows only. N.b. dealbreaker is also the fact that origin spreadsheet needs to have same column layout of destination spreadsheet. Thx 👌
Alan - this was a wonderful video on the use of Vlookup with the Column and Columns functions. Thank you ! I will now add these "tricks" to my Excel skill set. Keep on making these wonderful Excel instructional videos!!
Simple and effective. I prefer to uae Match function when the column labels are same but are at different positions in table array. Thanks for sharing 👍
You're welcome, Sachin. I think simple and effective sums it up nicely. MATCH can handle more complex scenarios for us. Each option has its strengths and weaknesses.
Thanks Bart, yes this is true. If the table started in column C we could subtract 2 form the returned column or set up a cell we can reference with the starting column in.
Hi Alan. Great tips. When using COLUMN, worth mentioning that COLUMN returns the absolute column number from the worksheet, not the relative position from within the table. So, using COLUMN for the col_index_number in this context only works if the table_array begins in Column A. If not, then other methods would work, such as using MATCH to coax the relative position of the column in the table by column header. Thanks for all the great videos at Computergaga. Always something new and interesting to learn here. Thumbs up!!
Thank you, Wayne. Your comments are appreciated. Yes, the absolute position. It would be awesome if it could return the table column when tables are used. Nevermind. If we knew the table started in column D, we could subtract 3 to keep the columns correct as a workaround.
@@Computergaga Thanks Alan. That would be great.. something like =COLUMN(TableName[ColumnName.ColumnNumber]) to return the relative column number of the specified table and the indicated column name. Until then, given that I always get users who do things I don't expect, in this circumstance, I'd likely use something like: =COLUMN(target table column)-COLUMN(first table column)+1. That would protect from any column insertions in the middle of the table or to the left of the the first table column. Thanks again for your always interesting videos and thought provoking topics. Thumbs up!!
Great stuff Alan. Like someone mention MATCH() is an alternative but it also needs the column name to be typed. The previous week setup was good too. Thanks
Here the Table array of v lookup is from the very 1st column, so y the Column function worked. But if the table array doesn't start from the very 1st column, then column function is not working ,rather *columns* function is working.
Love the column() trick - but that will ONLY work as long as the referenced "range formatted as a table" starts in column A. If it starts in column B, it will return one column to the right of the desired one, and if it starts in column C, it will return two columns to the right of the desired one. That is why I prefer to use columns() instead.
Yes, this is true. It is the column of the sheet. To counter this and still enable column selection, you could minus the number of preceding columns. So if the table start in C then minus 2.
@@Computergaga Absolutely right, and this will always work - as long as you (or whoever you are writing the workbook for) - don't then unwittingly change the table's position or insert a column. Ideally the formula would be dynamic/portable to avoid such an issue, in which case creating a formula to return the correct value becomes more complicated. COLUMNS can easily be made absolutely portable if, when entering the range argument, you click & drag to highlight from the first table column to the column you need - or, exactly as in your 2nd example, you can use a range name, which is always dynamic & is vital to your point - rather than entering a hard number.
Thank you, can you show the return Col # from the same SHEET? the same sheet has two tables for example =vlookup( F2,H2:W10, "I need to return the col N in table2")
Great tutorial. Amazing trick. For some reason my excel table nomenclature doesn't update automatically. It sometimes does and sometimes not. Very bizarre. Have u ever encountered this before?
Thank you, Nader. Is this when you select the column? You need to be careful to select the table column and not the sheet column. The arrow looks the same which makes it unclear.
@@Computergaga when I add a new row of data in the table, the other table that the formula doesn't get updated with the new data. I am sure the ranges are selected correctly as I could see the names of the sheet and coliumn names in the formula
Hi, for some reason, the download link returns me an empty file named vlookup-trick.xlsx (0 bytes length). Aniway, i use to use the match aproach. Thanks
These are great tips Alan and probably the better (smarter) way of using VLOOKUP. I too appreciate your clarity in your tutorials.
Thank you! Your comments are much appreciated.
Alan, this is one of the simplest video I've had ever seen at RUclips.
Short, precise and very much clear.
Thank you for sharing this.
Bless you!
You are very welcome. Thank you, Shehnil.
Great trick that avoids more complex formulas using MATCH. Thank you for sharing Alan!!!
My pleasure Iván. Thank you.
Hi Alan! Thank you for the trick. Your example shows a table format which is fine but for whoever is using a normal excel format (no table format), you can also use the same trick but the reference of the COLUMN () formula needs to be locked with absolute reference on the rows only. N.b. dealbreaker is also the fact that origin spreadsheet needs to have same column layout of destination spreadsheet. Thx 👌
Thanks Alan, great tip.
Thank you 👍
Great content! Thank you,
best trick I saw so far
Thank you very much 😊
Alan - this was a wonderful video on the use of Vlookup with the Column and Columns functions. Thank you ! I will now add these "tricks" to my Excel skill set. Keep on making these wonderful Excel instructional videos!!
Thank you, Douglas. Will do.
It seems so easy and simple, but I have never used it before. Really useful trick, thanks!
Awesome! Thank you.
very useful...thanks!!
You're welcome Samuel.
Simple and Brilliant, Thank you so much!!!
My pleasure. Thank you, Joanne.
nice tips, thanks a lot
You're welcome Reaz.
Simple and effective. I prefer to uae Match function when the column labels are same but are at different positions in table array. Thanks for sharing 👍
You're welcome, Sachin. I think simple and effective sums it up nicely. MATCH can handle more complex scenarios for us. Each option has its strengths and weaknesses.
@@Computergaga absolutely correct
Thank you
You're welcome, Akshay.
very helpful trick Alan, many thanks for sharing.
Kind regards
Thanks Mohideen.
I never thought about this, thanks for sharing the trick 😊
You're very welcome Sunand. Thank you 👍
Thanks Alan, great tip. I also tried "ranged names" that works with COLUMN as well, but you only need to start in column A.
Thanks Bart, yes this is true. If the table started in column C we could subtract 2 form the returned column or set up a cell we can reference with the starting column in.
great tip Alan, many thanks, Cheers Mohideen
Thank you, Mohideen
Hi Alan. Great tips. When using COLUMN, worth mentioning that COLUMN returns the absolute column number from the worksheet, not the relative position from within the table. So, using COLUMN for the col_index_number in this context only works if the table_array begins in Column A. If not, then other methods would work, such as using MATCH to coax the relative position of the column in the table by column header. Thanks for all the great videos at Computergaga. Always something new and interesting to learn here. Thumbs up!!
Thank you, Wayne. Your comments are appreciated.
Yes, the absolute position. It would be awesome if it could return the table column when tables are used. Nevermind. If we knew the table started in column D, we could subtract 3 to keep the columns correct as a workaround.
@@Computergaga Thanks Alan. That would be great.. something like =COLUMN(TableName[ColumnName.ColumnNumber]) to return the relative column number of the specified table and the indicated column name. Until then, given that I always get users who do things I don't expect, in this circumstance, I'd likely use something like: =COLUMN(target table column)-COLUMN(first table column)+1. That would protect from any column insertions in the middle of the table or to the left of the the first table column. Thanks again for your always interesting videos and thought provoking topics. Thumbs up!!
Excellent. Thanks Alan.
Best regards. Salim
Thank you, Salim.
Thanks
You're very welcome Nayan.
Thanks. It's a great trick and aditional way for use this function
Thank you, Alberto.
Nice one. I will certainly use this. Thx.
Great to hear. You're welcome.
Great stuff Alan. Like someone mention MATCH() is an alternative but it also needs the column name to be typed. The previous week setup was good too. Thanks
Thank you, Daniel. It is good to have alternative methods to fit different scenarios 👍
Great tip. Thank you.
You're welcome, Jan. Thank you.
Great tip! Thanks for sharing 🤗
You're very welcome, Immaculada.
Thanks Alan !! Great Tip
Thank you, Juan.
Really Enjoyed These Neat Tips...Great Stuff Thank You Alan :)
Thank you, Darryl.
Hello,
What can i say,thank you.I did not no this trick.You are good.
Thank you
You're welcome. Thank you Florin.
INDEX-MATCH for the win!
😄
great trick !!!!
Thank you, Kiasca.
Here the Table array of v lookup is from the very 1st column, so y the Column function worked.
But if the table array doesn't start from the very 1st column, then column function is not working ,rather *columns* function is working.
Love the column() trick - but that will ONLY work as long as the referenced "range formatted as a table" starts in column A. If it starts in column B, it will return one column to the right of the desired one, and if it starts in column C, it will return two columns to the right of the desired one. That is why I prefer to use columns() instead.
Yes, this is true. It is the column of the sheet. To counter this and still enable column selection, you could minus the number of preceding columns. So if the table start in C then minus 2.
@@Computergaga Absolutely right, and this will always work - as long as you (or whoever you are writing the workbook for) - don't then unwittingly change the table's position or insert a column. Ideally the formula would be dynamic/portable to avoid such an issue, in which case creating a formula to return the correct value becomes more complicated. COLUMNS can easily be made absolutely portable if, when entering the range argument, you click & drag to highlight from the first table column to the column you need - or, exactly as in your 2nd example, you can use a range name, which is always dynamic & is vital to your point - rather than entering a hard number.
Thank you, can you show the return Col # from the same SHEET? the same sheet has two tables for example =vlookup( F2,H2:W10, "I need to return the col N in table2")
Sure, you would select the table2 in the table array (second argument) of VLOOKUP.
Proper awesome
Thank you, David 😜
Sir! Not all heroes wear capes!
😊
for the second example, why is wk8's figures automatically grabbed? i.e. instead of wk5's etc-- is that the norm for COL function?
The COLUMNS function returns the number of columns in a given range. So when WK8 is added, it fetched the last column.
Damn useful ......Thanks B🙏
Excellent! You're welcome 👍
=VLOOKUP(F2,Products
Great tutorial. Amazing trick. For some reason my excel table nomenclature doesn't update automatically. It sometimes does and sometimes not. Very bizarre. Have u ever encountered this before?
Thank you, Nader. Is this when you select the column? You need to be careful to select the table column and not the sheet column. The arrow looks the same which makes it unclear.
@@Computergaga when I add a new row of data in the table, the other table that the formula doesn't get updated with the new data. I am sure the ranges are selected correctly as I could see the names of the sheet and coliumn names in the formula
nice job. please given the file in the description
Thank you, Ubaidillah. The file link is in the video description.
What we would do if category placed at column F instead of B 🤔🤔🤔
You can use the same technique and select column F.
Greet
Thank you.
Hi, for some reason, the download link returns me an empty file named vlookup-trick.xlsx (0 bytes length). Aniway, i use to use the match aproach. Thanks
Hi, it should all be working now Alvaro.
Can you apply it to hlookup?
Yes absolutely, but use ROW or ROWS instead
Excel XLOOKUP Trick - No More VLOOKUP (or HLOOKUP or INDEX/MATCH}
I love them all. I have no favourites 🤣
The excel download file seems to be corrupted or smthing
I see. I'll get this fixed later today.
It should be working now
@@Computergaga It works! Thanks!
Brilliant!
Just use xlookup Alan 😆
Never Charlie 🤣
Hahaha! Thanks for your videos Alan. Definitely helped me at work.
You're welcome. That is great to hear Charlie.
Sr, I am sorry if I hurt you
I'm not hurt Usman. The dowloadable file is working fine now 👍
@@Computergaga Thanks Sr, My pleasure. File is working now
Vlookup is dead.. Try xlookup instead..
Not yet my friend. VLOOKUP exists in millions of spreadsheets and XLOOKUP is Excel 365 only. Give it time.