Excel VLOOKUP Trick - No More Counting Columns

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.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[wmfield]

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

[shehnilsameer]

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

[IvanCortinas_ES]

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

[douglasbrown]

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

[prosurftoti]

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 👌

[SimpleExcelVBA]

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

[joanneosborne]

Simple and Brilliant, Thank you so much!!!

[sunandpal]

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

[mohideenthassim]

great tip Alan, many thanks, Cheers Mohideen

[mohideenthassim]

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

[sasavienne]

Excellent. Thanks Alan.
Best regards. Salim

[sachinrv]

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 👍

[albertopenalver]

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

[barttitulaerexcelbart]

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

[kjvstats]

Nice one. I will certainly use this. Thx.

[DanielLamarche]

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

[darrylmorgan]

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

[florincopaci]

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

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

[wayneedmondson]

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