Excel Magic Trick 1009: Lookup 3 Different Arrays From 3 Different Tables: VLOOKUP, CHOOSE, INDEX

See a formula that will lookup three 1 x 4 arrays and then multiply the first two arrays and divide the result by the third array:
1. VLOOKUP to lookup multiple columns using an array constant and a function argument array operation
2. CHOOSE function to lookup one of three array constants
3. INDEX and MATCH function to lookup a one by four row of values
4. SUMPRODUCT function to harvest all three "looked up arrays" and then multiply and divide them to get the final result.
5. VLOOKUP, CHOOSE and INDEX functions can all return arrays.

[pmsocho]

No way! You've got to be kidding me! :)
Sure enough, so many good tricks here!

[excelisfun]

Thank you for buying the DVD and book and supporting the cause! You should post a video of the great Excel things that you do!

[excelisfun]

Even though I made the video, I am similarly amazed at how many cool lookup things there are to know and do. This was one of the most fun videos to make in a long time... or at least the most fun since the MMULT video a few videos ago. Even given that MMULT video, this one was way more fun...

[excelisfun]

I am glad that you like the videos! Thank you for your support in buying the DVD! It is quite a good series of videos, especially Video #1 and #24, each are hour long videos with 25 or more examples...

[anirudhsk]

Mike, a new trick again. Amazing as always!

[excelisfun]

Cool - I am glad that you learned a lot from the video!

[animeshjaiswal]

As usual another awesome video posted by you. I have learnt a lot from these videos. Pre-ordered a DVD-ROM copy of your next book Ctrl+Shift+Enter on Amazon. Will be receiving it in the first week of July.

[texdess]

Mike, this is truly inspiring. Creativity with multiple functions at it's best. Keep it up!

[GrLayks]

EIF...you are truly the Excel Zen Master!

[krn]

Stupendous formula. How do you come up with these examples? Somebody must have sent you this... I'm floored Mike, great job.

[worldfamous]

Thank you!!! Please keep posting videos!!

[excelisfun]

Post #1
Glad you like it krn14242!

I come up with new problems from either other people or from my own day to day problem solving. This particular one is a combination of a question from someone else and my own misinterpretation of the problem.

[decpec]

Hello, I have learned so much from your video's. thank you.

[excelisfun]

Post #3
Formula elements like “function argument array operation”, “Lookup Row”, “If you put n items into a function argument, the function delivers n items”; or other formula elements not in this video like “Array of relative positions” or “Lookup Last Number” or “Lookup last text” or “Big Number” or “Big Text”…

In this way it is easy to see a given problem as a combination of different smaller solutions, then search find the parts and put them together...

[excelisfun]

Post #2
How do I come up with solutions? I have the different formula elements categorized in my mind so that when I see a new problem and search the index of formula elements in my mind and then mix and match the different formula elements to find a solution. This is why in my next book, Ctrl + Shift + Enter, I try hard to name the different formula elements and then be consistent with the names. Formula elements like “function argument array operation”, ... more in post #3

[excelisfun]

Okay, I will! i'll keep posting the vids!

[ExcelStrategy]

At the end of the video the formula is about to jump of the screen !!!!

[excelisfun]

No. I have never been tested, but I am sure it is very low. My ability to do stuff comes from one thing only: practice, practice practice, repeat, repeat, repeat. I call it the Sledge Hammer Method Of Learning Stuff.

[Lucas-zb]

I need your help.. I have 4 columns as below

Line    submin     submax     range           output
1         487          528             480-529
2         517          554             530-579
3         541          582             580-629
4         545          580             630-679
5         569          611             680-729

I need to know which Lines belong to each range. I'd expect something like below

Line    submin     submax     range           output
1         487          528             480-529      1, 2
2         517          554             530-579      1, 2, 3
3         541          582             580-629      3, 4, 5
4         545          580             630-679    
5         569          611             680-729

Thank you in advance,
Alex    

[tartarnus]

can you help me in my problem my mind will blow cause i cant find the solutions. . .please. . .