how to get Nearest higher value

[snjpverma]

the data in Column G is in ascending order and I want the number from Column G which is closest to the value in A2 as my return value.

the below lookup function gives me the closest lower value only. Please advise me how to get the closest higher value.

=LOOKUP(A2,G:G)

 

[Caleeco]

Hey snjpverma,

You could use an ARRAY formula

=INDEX(G:G,MATCH(MIN(ABS(G:G-A2)),ABS(G:G-A2),0))

Remember paste in the formula, and then hit CTRL+SHIFT+ENTER to create an array formula {}

Hope that helps
Caleeco

 

[snjpverma]

Hi Caleeco,
I tried Your formula and it gives me the nearest lower value instead of the higher one

 

[snjpverma]

I tried the below but it doesn't work either. it seems to work only in Descending order. but my data is in ascending.

=INDEX(G:G,MATCH(A2,G:G,-1))

 

[jtakw]

Hi,

Try this:

=INDEX(G:G,MATCH(A2,G:G,0)+1)

 

[Caleeco]

Hi Caleeco,
I tried Your formula and it gives me the nearest lower value instead of the higher one

Strange. Works on my test sheet.

Do you want the Nearest value? or the Nearest-Highest Value?

Caleeco

 

[AhoyNC]

If it's possible that the value in A2 is not always an exact match for a value in G try:

Excel Workbook
	A	B	C	G
1				46
2	204	237		75
3				78
4				94
5				102
6				108
7				167
8				194
9				237
10				270
11				308
12				328
13				330
14				337
15				383
16				393
17				404
18				420
Sheet

 

[Dave Patton]

Try either of the following

Excel 2010
	A	B	C	D
2	204		237	237
9b

Cell Formulas
Range	Formula
D2	=AGGREGATE(15,6,1/($G$1:$G$18>A2)*$G$1:$G$18,1)
C2	{=MIN(IF($G$1:$G$18>=$A2,$G$1:$G$18))}
Press CTRL+SHIFT+ENTER to enter array formulas.