Excel Magic Trick 1316: VLOOKUP with Multiple Lookup Tables: IF, INDIRECT, SWITCH, IFS, or CHOOSE?

Download Excel Files:

See how to use VLOOKUP when there are multiple Lookup Tables with these methods:
1. (00:15) Problem Set up
2. (01:48) IF Function
3. (05:57) IFS Function (Excel 2016 Insider Program) (Formula from XLarium at YouTube)
4. (08:47) SWITCH Function (Excel 2016 Insider Program)
5. (11:26) Names Ranges & INDIRECT Function?
6. (15:15) Summary

[bamakaze]

We were just talking the other day whether switch or choose was better. We all agreed to "choose." But now, after watching this video, switch is the right job for multiple table lookups. Thanks GM Mike!

[IICY]

After watching your videos I feel like I can become an excel God! I wonder what actual Excel Gods feel like...
Really educational and helpful to my work. Many, many thanks!

[LanceNYC]

Great presentation - nice and tight, respectful of our time. Everything make so much sense when the presenter knows his stuff and prepares his material. Really enjoyable. Thank you!

[rodrigocustodio]

Mike, you are very talented and you have this wonderful way of teaching, your delivery is awesome! Thank you!!

[TheOndricek]

Nice stuff! I formatted the ABC, EDS, and EDR boxes as Excel Tables. Then I changed the table names to ABC, EDS, and EDR respectively. That way if I need to add extra values to those boxes the named ranges will change automatically.

[marygracepagsanjan]

This is a complete and very substancial tutorial keepit up sir ! Godbless 😊

[garrybye]

Thank you so much. What a fantastic library of videos you have created - I love your approach and enthusiasm.  By far the best thing i've seen on YouTube.  I will gladly watch every advert in full to pay you back in a tiny way

[christoslefkimiotis]

the biggest shock was that you didn't use a formula array. My first thought was to use offset function and the rows down would be the match value from the cell on the left, thus if you add more tables you would rather insert them and the function will automatically update, nice video.

[usmanimtiaz]

You are an expert! I like your videos and appreciate your work which you are doing free for us.

[malblawi]

man, you fix my code with one minute ! thank you so much, god safe you

[brianxyz]

You don't need to be part of the Insiders program to get the new functions. You just have to wait 3 or 4 months until whatever channel you're in gets updated. It could be longer depending on your company as they can withhold updates longer if they want. I'm in the Deferred Channel stream for Office 365 Pro Plus and have had Indirect, etc. for awhile now.

[Nindzsaaa]

It is always entertaining, and useful. Thx for the lesson!

[paulbloomer]

Thanks, this is just what I needed for my dashboard report.

[Luciano_mp]

Sensacional, excelentes exemplos, obrigado. Mike você é o cara...

[sachinrv]

Hi, I used to use combination of IF, ISERROR and VLOOKUP. But this one is way better than this... thanks for sharing

[Sal_A]

We can make the formula shorter since we are doing approximate match. After you make defined table names, enter: =LOOKUP(B5, INDIRECT(A5))

[TheMarcosWilker]

thank you very much for your lessons. It's a pity it has so few visualizations compared to other videos in entertainment channels. Looks like people don't like studying excel.
Nobody explains and master excel like you.

[BillSzysz]

Wooow... Wide range of solutions !!!
Thanks Mike :-)

[syedsikander]

You are great Sir.... learned much more at sitting in my Thanks ! i am looking for a data base on inventory and store. like (Item list with Item code) (Received Material) ( Material Issued) (Stock Balance) on daily bases date wise.... hope you will make it to solve this issue...

[mnowako]

Couldn't be done better Mike! Thanks a lot!