Google-sheets – Find the closest value within a range of cells and output cell reference

google sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

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.

Best Answer

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.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.

Google Sheets – Find Closest Value in Range

Two options to try:

=IF(ISNUMBER(A2),INDEX(SORT(SAC!F:F,SAC!E:E,0),MATCH(A2,SORT(SAC!E:E,SAC!E:E,0),-1)),)

=QUERY(SAC!E:F,"select F where E >= "&A2&" order by E limit 1",0)

It's a shame that VLOOKUP doesn't accept the -1 sort parameter, otherwise this would work:

=IF(ISNUMBER(A2),VLOOKUP(A2,SORT(SAC!E:F,1,0),2,-1),)

Best Answer

Related Solutions

Google Sheets – Make All Cells in Column Links from Values

Update

Code

Explained:

Example:

Google Sheets – Find Closest Value in Range

Related Topic