06-27-2019 10:23 PM
06-27-2019 10:23 PM
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.
06-29-2019 11:48 PM
06-30-2019 12:00 PMSolution
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
06-30-2019 04:51 PM
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.
06-30-2019 05:58 PM
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?
07-01-2019 03:01 PM
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.
07-01-2019 09:25 PM
Hi Sergei@Sergei Baklan
Thank you kindly again for your assistance. I am learning a lot, very much appreciated.