Excel Lookup Series #5: VLOOKUP Function 5th Example

See how to create an array in a VLOOKUP formula, and see how to have the VLOOKUP function look at a table on a different worksheet than the one the VLOOKUP function sits in.

See how to create an array in a VLOOKUP formula so that you do not have to put the array in the worksheet cells. Use curly brackets, commas and semi-colons to create an array right in your VLOOKUP formula. See array syntax. See how to put the table_array in a VLOOKUP formula!

See how to have the VLOOKUP function look at a table on a different worksheet than the VLOOKUP function.

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
• array
• syntax
• formula
• named
• range
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[Berean50]

I am however wondering if there is a way to remove the randomly selected person from the listing because this selection would no longer be eligible to be selected again (as in door prizes, a person can only win once) without doing this manually (ie: delete the drawn person from the list). I really like the Duelling videos with Mr.Excel You guys are the pinacle for Excel knowledge & teaching. Need we to go elsewhere? Naaa. Why settle for a Volkswagen when you can get a Cadillac! Thanks guys!

[ExcelIsFun]

@Berean50 , try:

Excel Magic Trick 302 or Excel Magic Trick 373 and Excel Magic Trick 276

[act110mort048]

hey there! i loved your videos (it's easy to understand), but i just have one question: how do you use the vlookup function so that i lookup_value won't be from the left? in simpler words, how can you lookup something from the right and get the value left to it? coz the vlookup function only looks up something from the left and get the value right to it, and i want the reverse to happen. like, i want to get a value from the left by looking up from the right.

thanks!

[ExcelIsFun]

Try these videos:

Mr Excel & excelisfun Trick 19: VLOOKUP to Left (MATCH & INDEX or LOOKUP)

[nyxfix]

Once again, you've done an amazing job with another tutorial. Would you mind teling me how to use the F9 key to randomly reselect for only ONE cell? When I press F9 both cells keep reselecting randomly. Thank you so much for your help, aways!

[ExcelIsFun]

F9 cannot work on just one cell.

[nyxfix]

@ExcelIsFun Thank you!

[ExcelIsFun]

I am glad that they help!

[gunguna09]

Thanks buddy, your videos are of great help You are a star.....:)

[ExcelIsFun]

I am sorry I do not know how to do this:

how to let the table adjusts its row numbers itself according to a random row number given

Try this site:

mrexcel[dot]/forum