Forum Discussion
Help using index and match to find value based on 3 criteria
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.
Hi SergeiSergeiBaklan
Thank you kindly again for your assistance. I am learning a lot, very much appreciated.
Kind regards,
calof1 , you are welcome