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

First, a request... Before you post any more data in this forum, please click on the

blue sentenceat 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 79789Try 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 79789Since 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.

This works great, thank you very much. I also figured out a way to do it writing a compound if statement as well.

Now that sounds like a lot of work!

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

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 79789This 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)))

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.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History