Attached the Google Drive link and we have to refer to scenario 2 sheet of the spreadsheet.


Here some value is given in rows and we need to find the closest value from this range of cells in a row. Input is also given in the sheet and it is very much related to Scenario 1 sheet given in the link where given values are arranged in a column.

Scenario 2 sheet contains the values arranged in rows instead of columns. So we need to find the closest value for the given input and also column number of that row which matches with the input value.

  • 1
    Vignesh, I've already written several formulas on this data, and it continues to change. So before I look at this again ... is this the FINAL request you will have on this data? Or is there going to be another one (two, three, more) requests after this to do something different with this data? For the sake of time and efficiency, if this is not the end goal, please explain the end goal now rather than later. Thank you. – Erik Tyler Feb 3 '19 at 4:50
  • Erik, sorry abt that.At this point of time, i would certainly tell that this is the last one on this front .thanks and looking forward for the response – Vignesh Rams Feb 3 '19 at 6:43
  • One more question, Vignesh. You said in this post that you want the nearest value and "column number." I just want to make sure you want the column NUMBER rather than the column LETTER. Please confirm. – Erik Tyler Feb 3 '19 at 18:10
  • Adding to the previous question, Vignesh, if you do mean "column number," do you mean "which column number on the sheet" (i.e., with column A being 1), or "which column number within the range (i.e., with column F being 1 in your sample data)? – Erik Tyler Feb 3 '19 at 22:04
  • Column A being 1 ..thanks Erik .Looking forward for the result ..as my timeline for the completion is nearing.Help much appreciated – Vignesh Rams Feb 4 '19 at 7:59

I have set up a new sheet ("Erik Help") with variations on these two formulas (these from B8 and B9 respectively), which are a hybrid of MARK's, sageco's and my own formulas:

=IF(((B7<F1)*(B7>AH1))+(B7>MAX(F1:1))+(B7<MIN(F1:1))>0,-1,ARRAY_CONSTRAIN( SORT(ArrayFormula({TRANSPOSE(F1:1),ABS(TRANSPOSE(F1:1)-B7)}),2,1),1,1))


These hybrid formulas seem to cover all of your constraints.

| improve this answer | |
  • Thanks much for the help Erik, i am doing backtesting on various scenarios with ur solution. – Vignesh Rams Feb 5 '19 at 0:07
  • Did complete round of testing ..Works fine and again thanks for the help Mate !!! – Vignesh Rams Feb 5 '19 at 12:25
  • Be sure to thank MARK and sageco, as well. Group effort on this one. Glad it worked out. – Erik Tyler Feb 5 '19 at 20:40
  • I did thank everybody ..Erik – Vignesh Rams Feb 6 '19 at 12:12
  • Perfect, Vignesh. – Erik Tyler Feb 6 '19 at 16:39

This does seem very identical to an older question/answer.

In fact, so close that you can get the two by just taking my previous solution and breaking it apart.

  1. =ARRAY_CONSTRAIN( SORT(ArrayFormula({TRANSPOSE(F1:1),ABS(TRANSPOSE(F1:1)-B7)}),2,1),1,1) to find the value.

  2. =MATCH(B8,A1:1,0) to find its column "number" where A = 1

I have entered the formula in one of the spaces in the sheet you linked. You should be able to spread it to the other rows.

You can also examine its constituent formulas to aid in understanding how the above works:

a. =ArrayFormula({TRANSPOSE(F1:1),ABS(TRANSPOSE(F1:1)-B7)})

b. =SORT(ArrayFormula({TRANSPOSE(F1:1),ABS(TRANSPOSE(F1:1)-B7)}),2,1)

| improve this answer | |

finding closest value within a range of cells spread across the rows

 {TRANSPOSE(F1:1), ABS(TRANSPOSE(F1:1)-B7)}), 2, 1), 1, 1)

| improve this answer | |
  • I'm not sure whether you or sageco first came up with the primary formula, but it's nice. Adding the other constraints from the OP is optimal. Whatever gets the job done, I say! – Erik Tyler Feb 4 '19 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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