Index & Match to return a number

[NilsYan]

Dear all,

I would like to use Index Match function to return e.g. "6", whenever the "Column" equals 3, and the "row" equals a number between 40 and 59. How can I do this

Thank you

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: center"] [/TD]
[TD="class: xl64, width: 64, align: center"]80-100[/TD]
[TD="class: xl64, width: 64, align: center"]60-79[/TD]
[TD="class: xl64, width: 64, align: center"]40-59[/TD]
[TD="class: xl64, width: 64, align: center"]20-39[/TD]
[TD="class: xl65, width: 64, align: center"]0-19[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]3[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]5[/TD]
[TD="class: xl67, align: center"]6[/TD]
[TD="class: xl67, align: center"]7[/TD]
[TD="class: xl68, align: center"]8[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]2[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl68, align: center"]5[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]3[/TD]
[TD="class: xl70, align: center"]4[/TD]
[TD="class: xl70, align: center"]5[/TD]
[TD="class: xl70, align: center"]6[/TD]
[TD="class: xl70, align: center"]7[/TD]
[TD="class: xl71, align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

You have two 6s in column D (40-59), which one do you want to return since the rows numbers are indentical in column A? The first one? The second one? Both?

in A6 put the value you're looking for in column A, e.g. 3
in A7 put the range header, e.g. 40-59

in A8
=INDEX(B2:F4,MATCH(A6,A2:A4,0),MATCH(A7,B1:F1,0))

 

[NilsYan]

Sorry, in column A, the A2 should be 1, instead of 3.

What if there is only a number in A7, instead of a range, let's say 42. How would the formula look like then?

 

[Aladin Akyurek]

Sorry, in column A, the A2 should be 1, instead of 3.

What if there is only a number in A7, instead of a range, let's say 42. How would the formula look like then?

Is this what you are looking for?

Book1
	A	B	C	D	E	F
1		80-100	60-79	40-59	20-39	0-19
2	1	4	5	6	7	8
3	2	1	2	3	4	5
4	3	4	5	6	7	8
5
6		3
7	42	6
8
Sheet1

In B7 control+shift+enter, not just enter:

=INDEX($B$2:$F$4,MATCH(A7,REPLACE($B$1:$F$1,1,FIND("-",$B$1:$F$1),"")+0,-1),MATCH(B6,$A$2:$A$4,0))

If not, try to specify the output you require a bit more precise.

 

[Fluff]

If you're happy to change your headers, you could use

Excel 2013/2016
	A	B	C	D	E	F	G	H
1		100	79	59	39	19	Row	3
2	1	1	2	3	4	5	Column	45
3	2	6	7	8	9	10		13
4	3	11	12	13	14	15
Appendix

Cell Formulas
Range	Formula
H3	=INDEX(B2:F4,MATCH(H1,A2:A4,0),MATCH(H2,B1:F1,-1))

 

[NilsYan]

Thanks all, everything works perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Aladin Akyurek]

You are welcome.