In the attached file, I have a database with three columns: year, quarter, and revenue.
I want to write a formula that takes as input the year and the quarter, and returns the revenue.
Using MATCH and INDEX, I could query either the year or the quarter - but I need both together.
Can you help me fix the formula?
Hello
your formula should read:
=INDEX($C3:$C$14;MATCH(1;($A$3:$A$14=$B$18)*($B$3:$B$14=$B$19));0)
Update: (error in parenthesis)
=INDEX($C$3:$C$14; MATCH(1;($A$3:$A$14=$B$18)*($B$3:$B$14=$B$19);0))
If the answer is correct or helped you to fix your problem, please click the check mark (
) next to the answer.
It works fine when the year is 2019, but when the year is 2018 - I always get “9012” as the result (the last entry), regardless of the querter.
I managed to correct it - there should be another “;0” just before the last one
. It should be: “=INDEX($C3:$C$14;MATCH(1;($A$3:$A$14=$B18)*($B$3:$B$14=B$19);0);0)”