Excel Magic Trick 1031: Four Way Lookup Formula To Deliver Multiple Items From 1 of 2 Lookup Tables

Download Excel Start File:

See two great formulas to do a four way lookup that spans two tables:
1. Array Formula with IFERROR, INDEX, SMALL, IF, ROW, ROWS functions from Ed
2. Array Formula with IF, ROWS, INDEX, AGGREGATE, ROW functions from Mike. Learn about Option 2 in screen tip for INDEX function: using reference argument in INDEX function to lookup something in one of two tables

[excelisfun]

The reason you should film your own is because you are much smarter than I about math and numbers. If you make videos, your unique genus with numbers and Excel will be appreciated by many and will help many!

[MyEXCELerate]

Hi Mike, Hi Jeffrey... I'm so excited to join the Online Excel Team! Just need to prepare my materials and i'll be up and running soon!
Happy to Share! :)

[excelisfun]

Amazon! Thanks for supporting the cause in buying the books!

[JonathanThompson]

Suggestion for a future video: Why you would want to use array formulas, pros and cons, etc. I know I can find out on my own and I probably will but you are the master and you are great at teaching so I thought I would suggest it. Thank you.

[excelisfun]

That is a very clever formula! Totally cool, jeffrey!

[krn]

Great alternate method Mike. Good job Ed, nice formula.

[excelisfun]

You are a formula generating machine! Way cool to have so much fun with Excel!! Go, jeffrey!!!!

When are you going to start your own YouTube Channel and join the Video Online Excel Team? The more the merrier!

[excelisfun]

Cool - I am glad that the video was useful!

[excelisfun]

Cool! We need your unique point of view blogging at YouTube. It is funny, jeffrey, the guy in this video Ed, just ask me to make another one of his formuals into a video and I said; "No, you have to make your own fun videos". Then he said "Yes!" It will be so much more cool to have you and Ed added to our awesome Online Excel Team. New perspectives, new ideas in the pot so that we all benefit! When You get your YouTube channel up, I will link to your site from the excelisfun channel. Can't wait!

[excelisfun]

No. You!! You should make videos! Get the program, Camtasia, and start making videos. It is so easy with Camtasia, you just turn it on and it captures whatever is on the screen. It is so much more fun when you take your Excel creations and share them live! Create a channel and I'll add a link at my channel!

I did check out the challenge. It is really amazing!

[excelisfun]

That is what the book, Ctrl + Shift + Enter: Mastering Excel Array Formulas) is all about! For every example in the book (and DVD) I discuss the pros and cons and show alternative methods for accomplishing the same goal. Did you buy the book yet?

[excelisfun]

Thanks for the great formula and the great problem with the two tables side by side - really fun to try and solve! Nice work Ed!

[elphau]

completely speechless ~~!! this is beyond awesome.

[excelisfun]

Yes, the reference argument! That only works when the tables are on the same sheet as the formula. When I have multiple tables, I usually use CHOOSE, but INDEX is great if tables on same sheet.

[ExcelStrategy]

The multiple INDEX area it's completely new to me !

[MyEXCELerate]

Hi Mike, thanks for this and i'm learning more.
By the way, i'm still using ver 2007 and hopefully acquire 2013 version soon.
Happy to share :)

[jeffweir]

Mike, the coolest formulas I ever wrote are at that formula challenge.
Google these three puppies:
Formula Challenge 010 - Two-way interpolation
Formula Challenge 006 - What's the largest number you can confirm is a prime
Formula Challenge 013 - Turn non-contiguous cells into a 1D range

These might make for interesting fodder for your future posts. And there's plenty more material at the other challenges.

[excelisfun]

I can't wait!! This is so much fun!

[jeffweir]

Mike, there's a much simpler approach. Instead of having merged cells containing the words 'Area 1' and 'Area 2', put 'Area 1' into cells C2:E2 and 'Area 2' in cells F2:H2. Then throw in a few salient named ranges so you can do this:

Then just pick out the non-zero cells. e.g. wrap a LARGE around and array enter over your four result cells if you want them in descending order.

[excelisfun]

BTW, for array formulas, there is not much difference between the versions...