excel match question

October 26, 2011 at 13:05:12
Specs: Windows 7
Hi, I have four columns of data. One labeled "year" with either 2009, 2010, or 2011 values in the below cells. the other three columns are labeled 2009, 2010, and 2011 with numerical values in the below cells. I need a way to match up the value in the "year" column with the corresponding numerical value in the columns labeled 2009, 2010, and 2011. I need a formula that looks in the year column and pulls back the number associated with the the column heading 2009, 2010, or 2011. Is this a job for index match?

Thanks,
Harry


YEAR 2009 2010 2011
2009 24 78 88
2010 545 576 87
2010 56 6876 89
2011 7 56 7
2010 5 75 887
2009 67 6 766
2011 575 56 79789


See More: excel match question

Report •


#1
October 26, 2011 at 16:42:08
First, a request...

Before you post any more data in this forum, please click on the blue sentence at the end of this post and read the instructions given via that link. You'll learn how to produce tables like I did below. Thanks.

You didn't say where you wanted the result to be displayed, but I think HLOOKUP would be a better choice than INDEX and MATCH.

Let's assume we start with this data table:

	A	B	C	D
1	YEAR	2009	2010	2011
2	2009	24	78	88
3	2010	545	576	87
4	2010	56	6876	89
5	2011	7	56	7
6	2010	5	75	887
7	2009	67	6	766
8	2011	575	56	79789

Try this formula in E2 and drag it down:

=HLOOKUP(A2,$B$1:$D$8,ROW(),0)

	A	B	C	D	E
1	YEAR	2009	2010	2011	Results
2	2009	24	78	88	24
3	2010	545	576	87	576
4	2010	56	6876	89	6876
5	2011	7	56	7	7
6	2010	5	75	887	75
7	2009	67	6	766	67
8	2011	575	56	79789	79789

Since my table starts i Row 1, the ROW() function in E2 returns 2 so HLOOKUP is looking at that Row. If you start your formula in a Row other than 2, manipulate the ROW() function so that it always returns 2 in the first Row with the formula.

e.g.

In Row 3, this will return 2: ROW()-1
In Row 47, this will return 2: ROW()-45
etc.

Using INDEX and MATCH, you'd need something like this, which is a bit more complicated:

=INDEX($B$2:$D$8,ROW()-1,MATCH(A2,$B$1:$D$1,0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 27, 2011 at 16:42:45
This works great, thank you very much. I also figured out a way to do it writing a compound if statement as well.

Report •

#3
October 27, 2011 at 16:50:46

Report •

Related Solutions

#4
October 28, 2011 at 16:27:47
	A	B	C	D	E
1	YEAR	2009	2010	2011	Results
2	2009	24	78	88	24
3	2010	545	576	87	576
4	2010	56	6876	89	6876
5	2011	7	56	7	7
6	2010	5	75	887	75
7	2009	67	6	766	67
8	2011	575	56	79789	79789

This was the compound if statement that i used for column E

=IF($A2=$B$1,B2,IF($A2=$C$1,C2,IF($A2=$D$1,D2)))


Report •

#5
October 28, 2011 at 18:32:52
Well, since it appears that there will always be a match, just use this:

=IF($A2=$B$1,B2,IF($A2=$C$1,C2,D2))

No need to check if A2 = D1 if you already know that A2 doesn't match B1 or C1.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question