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 :)
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).
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.
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!
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.
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).
Thank you
Thank you for sharing
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.
Wonderful Video. I loved the use of match and min inside vlookup. Brilliant :)
Swap vlookup for index and you don't need to put {2,3,4,5}, neater and more flexible formula
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!
what happens when the scores are the same..for ex final grade and Test one are both MIN?
thanks for the video, very helpful.
good sar
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.
Very complicated presentations