1

enter image description here

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
1

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.

enter image description here

  • Thanks much for the response – Vignesh Rams Jan 29 at 11:11
0

=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), 
  SUBSTITUTE(ADDRESS(1, VLOOKUP(IF(
  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))

0

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

AFTER OP MADE CHANGES:

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.