Index-Match to select header value

[MattChewy]

Hi there,
Having trouble working out this index match scenario.
I have a long list of years and scores in column A and B.
I need to populate column C by copying down, with the correct category (low, med, high, very high) from the header in the second table, based on a match for the year, and then the approximate less than match.

My best shot is this logically: =INDEX($H$1:$K$1,MATCH(B2,$G$2:$G$27,0),MATCH(C2,$H$2:$K$27,1))
But I'm not sure a two-dimensional array for the column match is working...

Any ideas?


temp image share

 

[Eric W]

I didn't type in your whole table, and I changed a few values for testing. (For future reference, using the HTML Maker in my signature makes it much easier for people to import and test your data - it may help you get faster answers.)

	A	B	C	D	E	F	G	H	I	J	K
1		Year	Value	Classification				Low	Med	High	Very High
2		1990	0.295	Low			1990	0	0.476	0.621	0.727
3		1990	0.635	High			1991	0	0.481	0.627	0.728
4		1990	0.577	Med			1992	0	0.482	0.624	0.723
5		1990					1993	0
6		1990					1994	0
7		1990					1995	0
8		1990	0.705	High			1996	0
9		1990	0.634	High			1997	0
10		1990	0.866	Very High			1998	0
11		1990	0.794	Very High			1999	0
12		1990					2000	0
13		1990					2001	0
14		1991	0.745	Very High			2002	0
15		1991	0.386	Low			2003	0
16		1991	0.714	High			2004	0
17		1991					2005	0
18		1991	0.805	Very High			2006	0
19		1991	0.648	High			2007	0
20		1991	0.345	Low			2008	0
21

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =IF(C2="","",INDEX($H$1:$K$1,MATCH(C2,OFFSET($H$2:$K$2,MATCH(B2,$G$2:$G$20,0)-1,0)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

But the D2 formula seems to do what you want. Let us know how it works for you.

 

[MattChewy]

Hi Eric,
Thanks for the HTML maker link and the solution (works great).
Only things I don't quite understand in your formula is how the final INDEXing is grabbing the correct header category.

For D2, you go from this:
INDEX($H$1:$K$1,MATCH(C2,OFFSET($H$2:$K$2,MATCH(B2,$G$2:$G$20,0)-1,0)))

To the before last step in the calculation:
INDEX($H$1:$K$1,1)

Which selects "Low" correctly, however the INDEX formula is as such: INDEX( table, row_number, column_number ) and so your "1" is indicating the row_number. Shouldn't the "1" have to be in the column_number for the INDEX formula to return the correct category spread out across the columns?

I didn't type in your whole table, and I changed a few values for testing. (For future reference, using the HTML Maker in my signature makes it much easier for people to import and test your data - it may help you get faster answers.)

	A	B	C	D	E	F	G	H	I	J	K
1		Year	Value	Classification				Low	Med	High	Very High
2		1990	0.295	Low			1990	0	0.476	0.621	0.727
3		1990	0.635	High			1991	0	0.481	0.627	0.728
4		1990	0.577	Med			1992	0	0.482	0.624	0.723
5		1990					1993	0
6		1990					1994	0
7		1990					1995	0
8		1990	0.705	High			1996	0
9		1990	0.634	High			1997	0
10		1990	0.866	Very High			1998	0
11		1990	0.794	Very High			1999	0
12		1990					2000	0
13		1990					2001	0
14		1991	0.745	Very High			2002	0
15		1991	0.386	Low			2003	0
16		1991	0.714	High			2004	0
17		1991					2005	0
18		1991	0.805	Very High			2006	0
19		1991	0.648	High			2007	0
20		1991	0.345	Low			2008	0
21

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =IF(C2="","",INDEX($H$1:$K$1,MATCH(C2,OFFSET($H$2:$K$2,MATCH(B2,$G$2:$G$20,0)-1,0)))) <tbody> </tbody>

<tbody>
</tbody>

But the D2 formula seems to do what you want. Let us know how it works for you.

 

[Eric W]

INDEX is a pretty robust function, with lots of options. If you look at the documentation, it says that the column number OR the row number is optional. What this really boils down to is that if you give INDEX a 1-dimensional range (like $H$1:$K$1), vertically or horizontally, all you need is a single value to choose the element you want.

If you want, you certainly can change:

INDEX($H$1:$K$1,MATCH(C2,OFFSET($H$2:$K$2,MATCH(B2,$G$2:$G$20,0)-1,0)))

to

INDEX($H$1:$K$1,1,MATCH(C2,OFFSET($H$2:$K$2,MATCH(B2,$G$2:$G$20,0)-1,0)))

and it will work fine. It just depends on your style of designing formulas.

 

[MattChewy]

Good to know. Didn't realize that the formula was flexible in that way.
Thanks again for the great formula.

 

[Leo Skywalker]

Hi there,

Using Eric's table; here's a formula without the OFFSET to prevent volatility, and to return an empty cell if there is no VALUE; formula in D2:

IF(ISBLANK(C2),"",INDEX($H$1:$K$1,MATCH(C2,INDEX($H$2:$K$21,MATCH(B2,$G$2:$G$21,0),0),1)))