# 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,HarryYEAR 2009 2010 20112009 24 78 882010 545 576 872010 56 6876 892011 7 56 72010 5 75 8872009 67 6 7662011 575 56 79789

See More: excel match question

#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()-1In Row 47, this will return 2: ROW()-45etc. 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
 Now that sounds like a lot of work!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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 •