Forum Discussion
Help using index and match to find value based on 3 criteria
Hi,
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,
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
=IFERROR( INDEX($C$12:$S$15, MATCH(1,INDEX(($C$12:$C$15=C$4)*($D$12:$D$15=$C$3),0),0), MATCH(TRUE,ISNUMBER(SEARCH($C$2,$C$10:$S$10)),0)), "N/A")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
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
=IFERROR( INDEX($C$12:$S$15, MATCH(1,INDEX(($C$12:$C$15=C$4)*($D$12:$D$15=$C$3),0),0), MATCH(TRUE,ISNUMBER(SEARCH($C$2,$C$10:$S$10)),0)), "N/A")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
HIiSergeiBaklan
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
=IFERROR(INDEX($C$12:$T$15, MATCH(1,INDEX(($C$12:$C$15=C$4)*($D$12:$D$15=$C$3),0),0), MATCH(TRUE,ISNUMBER(SEARCH($C$2,$C$10:$T$10)),0)+ShiftMedian), "N/A")Sure you may use simply +0 or +1 or +2 or +3 in that part of formula.
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
Hey,
you could use the SUMPRODUCT formula instead:
Have a look at this video to understand how to use it: https://www.youtube.com/watch?v=lzjc_eEISe8
