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

	Column B	Column C	Column D	Column E	Column F	Column G	Column H
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9
Row 10
Row 11
Row 12
Row 13

<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>
[TD="width: 64"][/TD]
[TD="class: xl65, width: 390"]Reference Table[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="class: xl64"]M users[/TD]
[TD="class: xl64, align: right"]0.6[/TD]
[TD="class: xl64, align: right"]2.6[/TD]
[TD="class: xl64, align: right"]4.6
[/TD]
[TD="class: xl64, align: right"]6.6
[/TD]
[TD="class: xl64, align: right"]8.6[/TD]
[TD="class: xl64, align: right"]10[/TD]

[TD="class: xl63"]BHCA [ M BHCA ][/TD]
[TD="class: xl63, align: right"]0.6[/TD]
[TD="class: xl63, align: right"]2.6[/TD]
[TD="class: xl63, align: right"]4.6
[/TD]
[TD="class: xl63, align: right"]6.6
[/TD]
[TD="class: xl63, align: right"]8.6[/TD]
[TD="class: xl63, align: right"]10[/TD]

[TD="class: xl63"]Erlangs [/TD]
[TD="class: xl63, align: right"]15000[/TD]
[TD="class: xl63, align: right"]65000[/TD]
[TD="class: xl63, align: right"]115000
[/TD]
[TD="class: xl63, align: right"]165000[/TD]
[TD="class: xl63, align: right"]215000[/TD]
[TD="class: xl63, align: right"]250000[/TD]

[TD="class: xl63"]VM 1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2
[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]

[TD="class: xl63"]VM2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2
[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]

[TD="class: xl63"]VM3[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2
[/TD]
[TD="class: xl63, align: right"]2
[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]

[TD="class: xl63"]VM4[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8
[/TD]
[TD="class: xl63, align: right"]8
[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]

[TD="class: xl63"]VM5[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2
[/TD]
[TD="class: xl63, align: right"]2
[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]

[TD="class: xl63"]VM6[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40
[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]

[TD="class: xl63"]VM7[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]38[/TD]
[TD="class: xl63, align: right"]54
[/TD]
[TD="class: xl63, align: right"]70[/TD]
[TD="class: xl63, align: right"]86[/TD]

</tbody>

 

[DanteAmor]

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

[TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]=INDEX($A$1:$H$13,ROW(A4),MATCH($C$15,$A$4:$H$4,1)+1)[/TD]
[/TR]
</tbody>[/TABLE]


Copy the formula from C16 to C25

[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 80"][/TD]
[TD="width: 80"]Column B[/TD]
[TD="width: 80"]Column C[/TD]
[TD="width: 80"]Column D[/TD]
[TD="width: 80"]Column E[/TD]
[TD="width: 80"]Column F[/TD]
[TD="width: 80"]Column G[/TD]
[TD="width: 80"]Column H[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD="class: xl63"]M users[/TD]
[TD="class: xl63, align: right"]0.6[/TD]
[TD="class: xl63, align: right"]2.6[/TD]
[TD="class: xl63, align: right"]4.6[/TD]
[TD="class: xl63, align: right"]6.6[/TD]
[TD="class: xl63, align: right"]8.6[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD="class: xl63"]BHCA [ M BHCA ][/TD]
[TD="class: xl63, align: right"]0.6[/TD]
[TD="class: xl63, align: right"]2.6[/TD]
[TD="class: xl63, align: right"]4.6[/TD]
[TD="class: xl63, align: right"]6.6[/TD]
[TD="class: xl63, align: right"]8.6[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD="class: xl63"]Erlangs[/TD]
[TD="class: xl63, align: right"]15000[/TD]
[TD="class: xl63, align: right"]65000[/TD]
[TD="class: xl63, align: right"]115000[/TD]
[TD="class: xl63, align: right"]165000[/TD]
[TD="class: xl63, align: right"]215000[/TD]
[TD="class: xl63, align: right"]250000[/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD="class: xl63"]VM 1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD="class: xl63"]VM2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD="class: xl63"]VM3[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD="class: xl63"]VM4[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD="class: xl63"]VM5[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD="class: xl63"]VM6[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD="class: xl63"]VM7[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]38[/TD]
[TD="class: xl63, align: right"]54[/TD]
[TD="class: xl63, align: right"]70[/TD]
[TD="class: xl63, align: right"]86[/TD]
[/TR]
[TR]
[TD]Row 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 15[/TD]
[TD="class: xl63"]User Input[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 16[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]6.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 17[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]6.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 18[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]165000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 19[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 20[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 21[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 22[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 23[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 24[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 25[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]C16[/TH]
[TD]=INDEX($A$1:$H$13,ROW(A4),MATCH($C$15,$A$4:$H$4,1)+1)[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.