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
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