Help using index and match to find value based on 3 criteria

Iron Contributor



I have a large data set that involves three criteria, being year, scenario and product.


I am hoping to use a formula to find find and return the relevant answer from the data table. Attached is an example.


I have tried index and match, yet to find the right combination.


Can someone please assist.


Many thanks,


7 Replies



you could use the SUMPRODUCT formula instead:


Have a look at this video to understand how to use it:


best response confirmed by calof1 (Iron Contributor)



The logic is bit unclear. You have 3 criteria - Scenario, Stock Code and Year. In source data you have no combination Stock Code=FXL and Year=1, however in answers you suggest 5% for Year=2. Why?


If ignore above formula could be


Couple of more comments. Your file is practically not downloadable, perhaps due to comma in name - not all system support it.

I'd recommend not to use Merging Cells. Potentially you will have lot of issues continue using merging. Alternative is - select sequential horizontal cells and apply Center Across Selection


Hi@Sergei Baklan 


Thank you again for your help. Your solution works perfectly, and i appreciate your additional knowledge regarding merge cells. As a relatively new excel user i greatly appreciate all information and assistance.


Thanks again


HIi@Sergei Baklan 


Thanks for your help. 


From the formula i notice it looks at case, scenario, year. I will columns for min, median, max and sd for each of these. Can this criteria be added to the formula?


Kind regards,



To simplify the things you may define constants in Formulas->Name Manager as


when formula for the median is modified as


Sure you may use simply +0 or +1 or +2 or +3 in that part of formula.

Hi Sergei@Sergei Baklan 


Thank you kindly again for your assistance. I am learning a lot, very much appreciated.


Kind regards,