Excel Lookup Series #2: VLOOKUP Function 2nd Example

See how to use the VLOOKUP function to deliver a value to cell from the 3rd column of the table using the COLUMN function.

Find exact value from column 2 and column 3 of lookup table using the COLUMN function.

Use Named Range, Data Validation and VLOOKUP to make your lookup more accurate.


In This Series learn 15 amazing ways to look things up in Excel. We will look at the functions VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, CHOOSE, and the non-function lookup formula using the intersector operator. We will look at simple lookups all the way to complicated, yet efficient methods to look things up in Excel.

This is a logical (beginning to end) story about most of the lookup situations you may encounter in Excel.

Category:

Science & Technology

Tags:

• Excel
• VLOOKUP
• function
• exact
• value
• column
• COLUMN
• name
• data
• validation
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[apacjoe]

Question though, lets say I have i insert a row and type into cell B43 "Boom02", Do I have to start type a whole other vlookup all over again or can I just drag the same formula into C43 and D43? I saw that you locked it in with the "$" for B42, so I just have to rewrite the whole vlookup for another boom #? or do I just not lock it in with the $?

[ExcelIsFun]

@apacjoe , try this video:

Excel Magic Trick #397: VLOOKUP Copied Down a Column (VLOOKUP Basics)

[lvanny127]

Hi great stuff! However, the COLUMN(b1) confuses me. I don't understand why b1 would refer to column C?

[ExcelIsFun]

@lvanny127 , COLUMN(B1) yields a 2 because B is the second column. There is a more robust way to increment numbers in formulas: see this video:

Highline Excel Class 40: VLOOKUP 11 Unusual Examples

look at 5:00 minute mark

[arofairy]

@ExcelIsFun You are amazing teacher. what a nice way top teach, I'm grateful to you to make me learn. but I'm afraid I am confuse about COLUMN(B1) onward..

[ExcelIsFun]

@arofairy , COLUMN(B1) yields a 2 because B is the second column. There is a more robust way to increment numbers in formulas: see this video:

Highline Excel Class 40: VLOOKUP 11 Unusual Examples

look at 5:00 minute mark

[mmilos11]

the best tutorials!!!!!!!!!!

[ExcelIsFun]

I am glad that they help!

[Ache87]

Thank you! Great video, I learned in 6 minutes something that I couldn't learn in one year!

[ExcelIsFun]

You are welcome!

[capsaicinvirus]

hey dude! ur great! thanks a lot! such a great help! :D