# Find the closest value within a range of cells and output cell reference There are values from `F` column to `AJ` column in row 30. My input would be available in `C` column in row 30. As given in the picture, my input would be `118.45`, I would like to find the closest value to `118.45` within the range of cells from column `F` to `AJ`.

If we do it manually, I wud get `119.94` which is column `U` and row 30. Along with the value `119.94`, I would like to get the row and column reference (`U30`). Possible to implement with some multiple functions in google sheet.

Help would be really appreciated.

• Hi, Vignesh. Please include a link to your sheet here, being sure to set permissions to "Anyone with the link can Edit" (not just "...can View"). That will be the easiest way for us to help you. Also, when you say you want to "find the closest value," do you mean closest whether higher or lower? Or do you mean the next highest value? Which cell should hold the formula that returns the result? Which cell should hold the formula that tells the cell reference? – Erik Tyler Jan 28 at 23:14
• Thanks much for the response. – Vignesh Rams Jan 29 at 11:10

Presuming you only care about the absolute shortest distance to the entered number, the below should work:

``````=ADDRESS(ROW(),COLUMN()+
MATCH(ARRAY_CONSTRAIN(
SORT(ArrayFormula({TRANSPOSE(C30:30),ABS(TRANSPOSE(C30:30)-A30)}),2,1),1,1),C30:30,0),4)
``````

The idea is to create a "Table" with the list of values in the first column and the absolute difference in the second column. Sort by the smallest to the largest difference and return the first result.

Use the Match formula to find it in the row, then use Address to find the reference. • Thanks much for the response – Vignesh Rams Jan 29 at 11:11

``````=IFERROR(SUBSTITUTE(ADDRESS(1, VLOOKUP(C30,
{ARRAYFORMULA(QUERY(TRANSPOSE(F30:AJ30), "Select *", 1)),
{ARRAYFORMULA(ROW(INDIRECT("A1:A"&
COUNTA(QUERY(TRANSPOSE(F30:30), "Select Col1", 1)))))}}, 2, 0)+5, 4), 1, "")&ROW(C30),
INDEX(SORT(QUERY(TRANSPOSE(F30:30), "select Col1 where Col1>"&C30, 0), 1, 1), 1, 1)-C30>
INDEX(SORT(QUERY(TRANSPOSE(F30:30), "select Col1 where Col1<"&C30, 0), 1, 0), 1, 1)-C30,
INDEX(SORT(QUERY(TRANSPOSE(F30:30), "select Col1 where Col1>"&C30, 0), 1, 1), 1, 1),
INDEX(SORT(QUERY(TRANSPOSE(F30:30), "select Col1 where Col1<"&C30, 0), 1, 0), 1, 1)),
{ARRAYFORMULA(QUERY(TRANSPOSE(F30:AJ30), "Select *", 1)),
{ARRAYFORMULA(ROW(INDIRECT("A1:A"&
COUNTA(QUERY(TRANSPOSE(F30:30), "Select Col1", 1)))))}}, 2, 0)+5, 4), 1, "")&ROW(C30))`````` Vignesh, you didn't reply to my comment asking for more information. So I don't know where you want results or what you mean by closest. But let's say that "closest" means "closest whether that is higher or lower." And let's say that you wanted that number in D30 and the address of that number in E30:

In D30:

`=IF((C30-INDEX(F30:30,MATCH(C30,F30:30,1)))<(C30-INDEX(F30:30,MATCH(C30,F30:30,1)+1)),INDEX(F30:30,MATCH(C30,F30:30,1)),INDEX(F30:30,MATCH(C30,F30:30,1)+1))`

In E30:

`=ADDRESS(ROW(),COLUMNS(A30:E30)+MATCH(A30,F30:30,0),4)`

Since the second formula references the first, you must have the first in place in order to use the second (though you can use the first independently if you wish).

New formula pairings are variations of the following (with search value in H1):

In H2:

`=IF(((H1<B2)*(H1>B28))+(H1>MAX(B2:B28))+(H1<MIN(B2:B28))>0,-1,IF(ABS(H1-INDEX(SORT(B2:B28,1,1),MATCH(H1,SORT(B2:B28,1,1),1)))<ABS(H1-INDEX(SORT(B2:B28,1,1),MATCH(H1,SORT(B2:B28,1,1),1)+1)),INDEX(SORT(B2:B28,1,1),MATCH(H1,SORT(B2:B28,1,1),1)),INDEX(SORT(B2:B28,1,1),MATCH(H1,SORT(B2:B28,1,1),1)+1)))`

In H3:

`=IF(H2=-1,H2,MATCH(H2,B:B,0))`

NOTE TO READERS: This is such a specific issue that it's doubtful anyone else will need to reproduce what was done here (i.e., find the closest value to a search value within a list of values that starts arbitrarily high, climbs higher, ends arbitrarily, starts low and climbs, and ends arbitrarily; and returning -1 if the search value falls in any of the gaps).

• As suggested by you, i have given link of google sheet. In that sheet, i have given my requirement and also examples of what i require.Kindly provide me a way to access those functionality .help would be very much appreciated – Vignesh Rams Jan 30 at 15:37
• Here in the google sheet, i have used column to accommodate the range of values, if you feel those range of values should be arranged as rows, pls go ahead and change those thing accordingly. I was not sure which way is easiest(arranging those range of values as ROWS or COLUMNS) – Vignesh Rams Jan 30 at 15:57
• your help would be very much appreciated ..Thanks much – Vignesh Rams Jan 30 at 15:58
• Vignesh, you've already marked @sageco's answer as the correct answer, so it seems like you no longer need help. Is this correct? – Erik Tyler Jan 30 at 17:26