CODEWITHSUNDEEP
excelindexingmatchformulasumproduct
David D
David D

Reputation: 31

Find an intersecting value for each row and column in excel

I have monthly data in rows in column A. I also have monthly data in headers from column B to Column G. In Column H i have current month populated for all rows. I would like to display the value where, for example if the month in row is Jan and current month is Mar, then find the value where Jan and Mar intersect and do this for all rows. I tried using the following formula in Column J but it only gives the first value when it works. The result in Column I is desired. Attached is the picture for clarity.

enter image description here

=INDEX(B1:H2,MATCH(A2,$A$1:A2,0),MATCH(A2,$B$1:H2,0))

Upvotes: 0

Views: 1059

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

just use INDEX with one MATCH:

=INDEX(B2:G2,,MATCH(H2,$B$1:$G$1,0))

enter image description here

Upvotes: 2

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

You can do it with SUMPRODUCT.

I made a fake dataset with different values than the ones you show, so i could test properly if changing Current month would work.

It works even if column CurrentMonth holds different months

enter image description here

My formula is:

=SUMPRODUCT(--($B$1:$G$1=H2);B2:G2)

This is how it works:

  1. --($B$1:$G$1=H2) will return an array of 1 and 0 if range B1:G1 is equal to current month in that row. So in case CurrentMonth=Mar then it will return {0;0;1;0;0;0}
  2. With SUMPRODUCT we multiply previous array by range B:G in current row, so for row 2, it would be B2:G2. So as example, it would be {0;0;1;0;0;0} * {1;2;3;4;5;6} = {0;0;3;0;0;0} and we sum up this array, getting 3

Upvotes: 3

Related Questions