index match help needed

Hi,

I have been trying to seek a solution on the index match option when there are at least 2 variables involved and where the solution i am seeking is a text. I have attached a file with sample data

Sheet1 is my summary tab with a dropdown menu in cell C3, Country's in Column A and Years in Column B. I have tabs for countries which have the requisite data. Based on the product chosen in C3, I would want to see the comments appearing in Column C in the space provided.

i have used the Sumifs function for similar requirements but they only work for numericals and they dont seem to work for pulling text. I am guessing that Index match is the only solution. If I am wrong, please do correct me.

Thanks for your help.

Yes, Mangesh..thats right!

Hi,
for France you can use: =INDEX(France!$C$6:$C$17,SUMPRODUCT((France!$A$6:$A$17=$C$3)*(France!$B$6:$B$17=B6)*(ROW($A$1:$A$12))))
for Germany: =INDEX(Germany!$C$6:$C$17,SUMPRODUCT((Germany!$A$6:$A$17=$C$3)*(Germany!$B$6:$B$17=B6)*(ROW($A$1:$A$12))))

Thanks, Pepe and Mangesh...both your solutions make perfect sense and work very well. So as to avoid adding another 'helper col' in all the country tabs (and I have plenty!!), I have used the indirect function in the formula given by pepe and it works very well.

If you populate column A on sheet1 with countries (so A6:A9=France,A10:A13=Germany), than you can use: =INDEX(INDIRECT(A6&"!$C$6:$C$17"),SUMPRODUCT((INDIRECT(A6&"!$A$6:$A$17")=$C$3)*(INDIRECT(A6&"!$B$6:$B$17")=B6)*(ROW($1:$12))))

If you don't want to populate column A and keep just 1st field (so A7:A9 and A11:A13 are empty), it's still possible, but the formula will look like this:

=INDEX(INDIRECT(INDEX($A$6:$A$13,SUMPRODUCT(MAX(ISTEXT($A$6:A6)*ROW($1:1))))&"!$C$6:$C$17"),SUMPRODUCT((INDIRECT(INDEX($A$6:$A$13,SUMPRODUCT(MAX(ISTEXT($A$6:A6)*ROW($1:1))))&"!$A$6:$A$17")=$C$3)*(INDIRECT(INDEX($A$6:$A$13,SUMPRODUCT(MAX(ISTEXT($A$6:A6)*ROW($1:1))))&"!$B$6:$B$17")=B6)*(ROW($A$1:$A$12))))

confirmed by ctrl-shift-enter