Excel using INDEX and multiple MATCH

Question

I am having trouble using INDEX and MATCH functions in excel. Say I have the following data:

A        B       C      D
Year   Month   Site   Count
2004     3      X1      54
2006     6      X3      10
2005     10     X5      15

And I want to arrange it like this

E        F      G       H      I      J       K
Year   Month    X1      X2     X3     X4      X5
2004     1
2004     2
2004     3      54            
2004     4
2004     5
2004     6

...

2005     10                     10

...

2006     6                                   15

I have the following formula (I want to match the Site, Year and Month):

=IFERROR(INDEX($D$2:$D$4,MATCH(G$1,$C$2:$C$4,0),MATCH($E2,$A$2:$A$4,0),MATCH($F2,$B$2:$B$4,0)),"")

and it seems to work fine for the first column (G) but when I autofill the rest of the columns (H:K) it doesn't work. Any ideas? Thanks.

One problem is the Year Month combinations in cols A:B don't match the ones in E:F. — cpilko
– cpilko, Commented May 12, 2014 at 3:52
@cpilko oops sorry, that was a typo - they do now (and do in my data set) — user2861089
– user2861089, Commented May 12, 2014 at 3:54
It seems that the Count isn't going in the correct year/month row. Not sure why... — user2861089
– user2861089, Commented May 12, 2014 at 4:05
Is each row unique, or could there be multiple rows returned from March 2004? — cpilko
– cpilko, Commented May 12, 2014 at 4:10
@cpilko There are multiple entries for March 2004 but only one for Site X1 — user2861089
– user2861089, Commented May 12, 2014 at 4:22

cpilko · Accepted Answer · 2014-05-12 04:36:57Z

1

I'd take a different approach than using all these nested matches, and create a searchable column with unique values and MATCH that column to get a row to feed into the INDEX.

Insert 2 rows between Cols D and E, putting the 2nd appearance of 'Year' in Column G. (Because I like some whitespace)

Paste this formula in E2 and copy it to E3:E4

=CONCATENATE(C2,"-",A2,"-",B2)

If it bothers you, hide Column E.

Then paste this formula in I2 and copy it to I2:M4:

=IFERROR(INDEX($D$2:$D$4,MATCH(CONCATENATE(I$1,"-",$G2,"-",$H2),$E$2:$E$4,0),0),"")

answered May 12, 2014 at 4:36

cpilko

11.9k2 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel using INDEX and multiple MATCH

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related