Excel Lookup Series #5: VLOOKUP Function 5th Example

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

[gunguna09]

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

[ExcelIsFun]

I am glad that they help!

[xiaomianyang1956]

Thank you for replying, I figured that out, it is because i did not use alt-shit-enter to finish this array formula. If you don't mind, please look at my next question. Do yo know how to let the table adjusts its row numbers itself according to a random row number given?

Thanx again.

[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

[xiaomianyang1956]

Do you know how to use vlookup to get multiple entries with the same look-up values. For example:

Type Price

a $3

b  $5

a $4

How do i get all type "a" prices?

[ExcelIsFun]

Yes, but it complicated. I have many videos on this topic. Here are 3:

Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula

Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula

Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula

[xiaomianyang1956]

Do you know how to use vlookup to get multiple entries with the same look-up values. For example:

Type Price

a  $3

b $5

a $4

How do i get all type "a" prices?

[ExcelIsFun]

Yes, but it complicated. I have many videos on this topic. Here are 3:

Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula

Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula

Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula

[lecterrkr2]

I use the Spanish version for Excel, and in the table_array using the curly brackets was not working for me, it shows a formula error, then I downloaded your finished file, and there was reacomodated like this: {1;"Suix"\2;"Fred"\3;"Chin"\4;­"Sheliadawn"}... with the slash betwen, and semi-coloms in diferent position. Just like a curious point.

Thanks for your legacy... I'm really learning a lot.

[ExcelIsFun]

That is a cool point you offer!

I am glad that you are learning a lot!

[nicoyanicoya]

i am having trouble ....i try to vlookup from one worskeet to another but its not working...is there something that I have ti to turn on ?

[ExcelIsFun]

Search for and wacth this video title:

Highline Excel Class 07: VLOOKUP function formula 7 Examples

The example you want is after the 13 minute mark.

[nicoyanicoya]

Thank you for your reply,

I am still not getting an answer.

I receive a #N/A response

below is what my formula looks like:

=VLOOKUP(C8,'IFS FY ''10 Q2'!A123:Q149,4,FALSE)

do you see anything that coul dbe wrong?

would it help if i send you the sheets so that you can figure what i am doing wrong?

thanks

[ExcelIsFun]

This video title should help:

Excel Magic Trick 333: #DIV/0! Error IF & ISERROR or IFERROR functions

[26moron]

hi,

these videos are of g8 help , it will be of help if u can let me know how to download these excel sheets so that i can practice this also later...thankz fr these wonderful videos

[ExcelIsFun]

Go to the excelisfun channel, then on the lower left side (scroll down) is the blue link to my college web site.

Search YouTube for "excelisfun", then go to the channel - there is a video on the right side that shows how to download and search for any video that you would like!

[lubenitza2004]

The same thing with the names. What I am doing wrong? I put =VLOOKUP(RANDBETWEEN(1,4),..., 2) and Enter... The result #NAME?

[ExcelIsFun]

#NAME? error comes from a word in a formula that is not a defined name or function. RANDBETWEEN is a function not automatically added in in versions of Excel before 2007. In earlier versions you can add this function in by going to Tools, add-ins, then add the Analysis Toolpak.

An alternative formula that will do the same thing is:

=VLOOKUP(INT(RAND()*4+1),{1,"S­ioux";2,"Fred";3,"Chin";4,"She­iladown"},2)

Here we are substituting INT(RAND()*4+1) for RANDBETWEEN(1,4)

[razibrox]

good videos

[ExcelIsFun]

I am glad that you like them!

[XiaoBenJu]

Hello,

I am using MS Excel 2008 for Mac and what frustrates me the most is that I cant use my function keys on my macbook while you taught us to use them to get the shortcuts.

I am wondering if I dont use the function keys and follow the traditional ways, can you please teach me how to get to the same results?

Thanks

[ExcelIsFun]

I don't know how to use a Mac.

If you download some of the workbooks, the workbooks have notes about menues and Ribbons.

[chippendails]

hello,

I am from Germany so I use the german version of Excel. The problem is that all the time I use ";" instead of ",". it is no problem untill now, but I cannot create the table because I don't know what to use instead of the ";"

[ExcelIsFun]

Dear chippendails,

No problem. I have a video about this topic. Serach for and watch this video:

Excel Magic Trick #137: Array Syntax for USA & Norway

--excelisfun

[shiryu008]

for ten years! excel!!. Wow. Your are the Jeday Master Obi wan! :).

interesting the random function. Again, thanks a lot.

[ExcelIsFun]

Dear shiryu008,

no problem!

--ExcelIsFun

[Perfection02051982]

Hi ExcellsFun,

I really appreciate all your vlookup videos and wonder how many times and books it taked you to learn all these amazing things ?

[ExcelIsFun]

Dear Perfection02051982,

I am glad that you find the videos useful!! If you keep watching, I'll keep making the videos!

How do you learn lots of Excel Tricks? It's the same as all other endeavors in live: Practice, Practice, Practice!! I have read so many books I can't even count (the best are MrExcel and Walkenbach books). But the main way I have learned is doing Excel everyday for 10+ years and always doing Excel smartly (even though in the beginning it takes longer)..

--ExcelIsFun