No video :(

Excel VLOOKUP Trick - No More Counting Columns

Подписаться 106 тыс.

Просмотров 30 тыс.

50% 1

In this video, we demonstrate an excellent VLOOKUP trick that is not well known. It enables you to select a column to return, instead of specifying the column index number.
One of the biggest frustration with VLOOKUP, especially for beginners, is the need to count columns to find the column index number.
This simple VLOOKUP trick will stop that tedious task.
Two Excel VLOOKUP examples are shown to demonstrate the possibilities.
You can download the files used in the video here - www.computergaga.com/_excel/f...
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

Опубликовано:

8 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 106

@shehnilsameer755 3 года назад

Alan, this is one of the simplest video I've had ever seen at RU-vid. Short, precise and very much clear. Thank you for sharing this. Bless you!

@Computergaga 3 года назад

You are very welcome. Thank you, Shehnil.

@wmfield152 2 месяца назад

These are great tips Alan and probably the better (smarter) way of using VLOOKUP. I too appreciate your clarity in your tutorials.

@Computergaga 2 месяца назад

Thank you! Your comments are much appreciated.

@IvanCortinas_ES 3 года назад

Great trick that avoids more complex formulas using MATCH. Thank you for sharing Alan!!!

@Computergaga 3 года назад

My pleasure Iván. Thank you.

@douglasbrown3354 3 года назад

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!!

@Computergaga 3 года назад

Thank you, Douglas. Will do.

@mamadoubah3686 2 года назад

Great content! Thank you,

@joanneosborne2428 3 года назад

Simple and Brilliant, Thank you so much!!!

@Computergaga 3 года назад

My pleasure. Thank you, Joanne.

@kaleem77 2 года назад

Thanks Alan, great tip.

@Computergaga 2 года назад

Thank you 👍

@SimpleExcelVBA 3 года назад

It seems so easy and simple, but I have never used it before. Really useful trick, thanks!

@Computergaga 3 года назад

Awesome! Thank you.

@prosurftoti 3 года назад

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 👌

@barttitulaerexcelbart9400 3 года назад

Thanks Alan, great tip. I also tried "ranged names" that works with COLUMN as well, but you only need to start in column A.

@Computergaga 3 года назад

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.

@mohideenthassim7180 3 года назад

very helpful trick Alan, many thanks for sharing. Kind regards

@Computergaga 3 года назад

Thanks Mohideen.

@albertopenalver1435 3 года назад

Thanks. It's a great trick and aditional way for use this function

@Computergaga 3 года назад

Thank you, Alberto.

@samuelkodjoe1645 3 года назад

very useful...thanks!!

@Computergaga 3 года назад

You're welcome Samuel.

@reazkhanchowdhury421 3 года назад

nice tips, thanks a lot

@Computergaga 3 года назад

You're welcome Reaz.

@imm1653 3 года назад

Great tip! Thanks for sharing 🤗

@Computergaga 3 года назад

You're very welcome, Immaculada.

@sunandpal 3 года назад

I never thought about this, thanks for sharing the trick 😊

@Computergaga 3 года назад

You're very welcome Sunand. Thank you 👍

@sachinrv1 3 года назад

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 👍

@Computergaga 3 года назад

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.

@sachinrv1 3 года назад

@@Computergaga absolutely correct

@darrylmorgan 3 года назад

Really Enjoyed These Neat Tips...Great Stuff Thank You Alan :)

@Computergaga 3 года назад

Thank you, Darryl.

@juanramon8652 3 года назад

Thanks Alan !! Great Tip

@Computergaga 3 года назад

Thank you, Juan.

@kjvstats9003 3 года назад

Nice one. I will certainly use this. Thx.

@Computergaga 3 года назад

Great to hear. You're welcome.

@JanBolhuis 3 года назад

Great tip. Thank you.

@Computergaga 3 года назад

You're welcome, Jan. Thank you.

@DanielLamarche 3 года назад

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

@Computergaga 3 года назад

Thank you, Daniel. It is good to have alternative methods to fit different scenarios 👍

@mohideenthassim7180 3 года назад

great tip Alan, many thanks, Cheers Mohideen

@Computergaga 3 года назад

Thank you, Mohideen

@sasavienne 3 года назад

Excellent. Thanks Alan. Best regards. Salim

@Computergaga 3 года назад

Thank you, Salim.

@akshaykadam3228 2 года назад

Thank you

@Computergaga 2 года назад

You're welcome, Akshay.

@TheNayanshetty Год назад

Thanks

@Computergaga Год назад

You're very welcome Nayan.

@SeldonLien 2 года назад

best trick I saw so far

@Computergaga 2 года назад

Thank you very much 😊

@kiasca3489 3 года назад

great trick !!!!

@Computergaga 3 года назад

Thank you, Kiasca.

@florincopaci6821 3 года назад

Hello, What can i say,thank you.I did not no this trick.You are good. Thank you

@Computergaga 3 года назад

You're welcome. Thank you Florin.

@finldavi 3 года назад

Proper awesome

@Computergaga 3 года назад

Thank you, David 😜

@MrDhunpagla 3 года назад

Damn useful ......Thanks B🙏

@Computergaga 3 года назад

Excellent! You're welcome 👍

@memofromessex 3 года назад

INDEX-MATCH for the win!

@Computergaga 3 года назад

😄

@gamlielu Год назад

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")

@Computergaga Год назад

Sure, you would select the table2 in the table array (second argument) of VLOOKUP.

@wayneedmondson1065 3 года назад

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!!

@Computergaga 3 года назад

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.

@wayneedmondson1065 3 года назад

@@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!!

@tiff653 3 года назад

Sir! Not all heroes wear capes!

@Computergaga 3 года назад

😊

@azmf80 3 года назад

Greet

@Computergaga 3 года назад

Thank you.

@ubaidillahmuhammad20 3 года назад

nice job. please given the file in the description

@Computergaga 3 года назад

Thank you, Ubaidillah. The file link is in the video description.

@nadermounir8228 3 года назад

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?

@Computergaga 3 года назад

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.

@nadermounir8228 3 года назад

@@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

@anup6793 2 года назад

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.

@ardenzhuo9351 3 года назад

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?

@Computergaga 3 года назад

The COLUMNS function returns the number of columns in a given range. So when WK8 is added, it fetched the last column.

@chrismoule7242 3 года назад

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.

@Computergaga 3 года назад

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.

@chrismoule7242 3 года назад

@@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.

@tottochan249 Год назад

Can you apply it to hlookup?

@Computergaga Год назад

Yes absolutely, but use ROW or ROWS instead

@alvaroconsuegra 3 года назад

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

@Computergaga 3 года назад

Hi, it should all be working now Alvaro.

@MrDhunpagla 3 года назад

What we would do if category placed at column F instead of B 🤔🤔🤔

@Computergaga 3 года назад

You can use the same technique and select column F.