Excel (Advanced) - Sumproduct + Vlookup

Vlookup with column index as an array or vector nested in a sumproduct function. Additionally, this include match and min function all rolled up into one. Enjoy :)

[sachinrv]

Wonderful Video. I loved the use of match and min inside vlookup. Brilliant :)

[WWShorter]

Another approach is to use the CHOOSE function instead of the VLookup in the same sumproduct formula. The MATCH MIN combo can provide the position number for CHOOSE and the 4 locked ranges can be in the positions 1 through 4 at the end of the CHOOSE function. Yields same result without the brackets for range array used in your VLookup example, which may confuse less experienced folks. Also, VLookup can be a memory hog. So this may speed up calculation.

[EVUTube]

Formula efficiency only really becomes significant in very large data sets, so it is not an issue with only 80 students, but you could eliminate the MIN function (and its associated array operation) by setting the MATCH lookup value to 0 and the match type argument to -1.  Sort of like this:  MATCH(0, B4:E4, -1).  In this construction, the function will lookup the smallest value that is greater than or equal to the lookup value (0).

[drgeorge]

Congrats for your magnificent channel! All the best! After I realised the logic of your example, I ran a similar example, using exactly the same function (nested vlookup in Sumproduct). The returned result was "#VALUE". I am trying to figure out what went wrong. Needless to say that you' ve helped me considerably in my professional life. Your tutorials are always explanatory and pretty helpful! Cheers!

[vhc]

Swap vlookup for index and you don't need to put {2, 3, 4, 5}, neater and more flexible formula

[wcthrill]

what happens when the scores are the same..for ex final grade and Test one are both MIN?

[parasshah]

Hi Jalayer,


I have data across e.g. 12 sheets and I would like to add a particular data from all the sheets, is there a formula that can use Vlookup and sumproduct to achieve this?

Let me know if you need more information to assist.