Search a cell value in range with match to higher value and return column results using excel formula

[gmalpani]

Hi Experts,

I have a reference table where values are defined for specific subscriber value.
The user will input a value which shall be search in reference table. There will not be exact match in reference table for cell value.
So, may be user enters 6M users and reference table may have 4.6M and 6.5M. So, in this case values shall be picked up from column with 6.5M (higher than user input). It is required to return value for all corresponding cells. This is required to be done using formula not macro.

So, below is the reference table.
First row shall be considered to match user input.
User input 6 (in cell C15), so this shall be check in first row and 4.6<6<6.6. So, column F shall be copied from F4:F13 to column C16:C25

	Reference Table
	Column B	Column C	Column D	Column E	Column F	Column G	Column H
Row 4	M users	0.6	2.6	4.6	6.6	8.6	10
Row 5	BHCA [ M BHCA ]	0.6	2.6	4.6	6.6	8.6	10
Row 6	Erlangs	15000	65000	115000	165000	215000	250000
Row 7	VM 1	2	2	2	2	2	2
Row 8	VM2	2	2	2	2	2	2
Row 9	VM3	2	2	2	2	2	2
Row 10	VM4	8	8	8	8	8	8
Row 11	VM5	2	2	2	2	2	2
Row 12	VM6	40	40	40	40	40	40
Row 13	VM7	6	22	38	54	70	86

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:14262;width:293pt" width="390"> <col style="width:48pt" width="64" span="6"> </colgroup><tbody>
</tbody>

 

[DanteAmor]

You need a formula for each cell from C16 to C25.
The formula in C16 is

=INDEX($A$1:$H$13,ROW(A4),MATCH($C$15,$A$4:$H$4,1)+1)

<tbody>
</tbody>

Copy the formula from C16 to C25

	Column B	Column C	Column D	Column E	Column F	Column G	Column H
Row 4	M users	0.6	2.6	4.6	6.6	8.6	10
Row 5	BHCA [ M BHCA ]	0.6	2.6	4.6	6.6	8.6	10
Row 6	Erlangs	15000	65000	115000	165000	215000	250000
Row 7	VM 1	2	2	2	2	2	2
Row 8	VM2	2	2	2	2	2	2
Row 9	VM3	2	2	2	2	2	2
Row 10	VM4	8	8	8	8	8	8
Row 11	VM5	2	2	2	2	2	2
Row 12	VM6	40	40	40	40	40	40
Row 13	VM7	6	22	38	54	70	86
Row 14
Row 15	User Input	6
Row 16		6.6
Row 17		6.6
Row 18		165000
Row 19		2
Row 20		2
Row 21		2
Row 22		8
Row 23		2
Row 24		40
Row 25		54

<tbody>
</tbody>

Worksheet Formulas

Cell	Formula
C16	=INDEX($A$1:$H$13,ROW(A4),MATCH($C$15,$A$4:$H$4,1)+1)

<tbody>
</tbody>

 

[Fluff]

Another option

Excel 2013/2016
	A	B	C	D	E	F	G
1
2
3
4	M users	0.6	2.6	4.6	6.6	8.6	10
5	BHCA [ M BHCA ]	0.6	2.6	4.6	6.6	8.6	10
6	Erlangs	15000	65000	115000	165000	215000	250000
7	VM 1	2	2	2	2	2	2
8	VM2	2	2	2	2	2	2
9	VM3	2	2	2	2	2	2
10	VM4	8	8	8	8	8	8
11	VM5	2	2	2	2	2	2
12	VM6	40	40	40	40	40	40
13	VM7	6	22	38	54	70	86
14
15			10
16			10
17			10
18			250000
19			2
20			2
21			2
22			8
23			2
24			40
25			86
data

Cell Formulas
Range	Formula
C16	{=INDEX($B$1:$G$13,ROW(A4),MATCH(TRUE,$B$4:$G$4>=C$15,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.