Ask Your Question
0

How to use MATCH and INDEX with multiple columns

asked 2019-06-17 10:23:38 +0200

Erel Segal-Halevi gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-06-17 12:17:13 +0200

Opaque gravatar image

updated 2019-06-17 12:47:14 +0200

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.

edit flag offensive delete link more

Comments

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.

Erel Segal-Halevi gravatar imageErel Segal-Halevi ( 2019-06-17 12:24:03 +0200 )edit

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

Erel Segal-Halevi gravatar imageErel Segal-Halevi ( 2019-06-17 12:26:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-17 10:23:38 +0200

Seen: 41 times

Last updated: Jun 17