Selecting a value by determining if a number lands between two values on a scale

User defined functions are custom functions used in the same way as Excel's built in functions (sum, if, lookup, etc.). UDFs are written in VBA (Visual Basic for Applications, the coding component of Excel). In order to use a UDF the code must be in the VBA of a workbook.

To add a UDF to a workbook, with the workbook open hit alt-F11, insert a module (insert, module). You should then see module 1. Copy the code I posted earlier into the module. Close the VBA Editor (alt+Q). Now you can use the UDF in a cell on the workbook (ScaleReturn). In order to save the VBA code in the workbook (ie. To have the function continue to work) you must save as a macro enabled workbook (What you posted was not a macro enabled workbook so you'd need to use save as and change the file type to macro enabled workbook). Hopefully this makes sense.

As I said before the 2 arguments for the function are the cell you want to check against your ranges (C2 on the sheet I believe) and the range for the lower end of your ranges ($I$2:$I$22). Full function being =ScaleReturn(C2,$I$2:$22).

Side note, have you learned what the $ in a function means? If not Google absolute vs relative references in Excel as this is very handy thing to understand.

Another quick note, your 2 number ranges start and end at the same numbers, while I really don't think this matters in your application as it does not look like you'd ever end up with an exact number, it does leave ambiguity on which range you'd want an exact match to fit into (does it match the highest number of one range or the start number of the range one row down?). Currently the way this is written it will match the lower number in your 2 number range further down the column, ie. return the higher value in your example. If you want this to return the lower value let me know and we can switch it. Like I say, looking at your column C2 values or looks really unlikely we'd be working with any whole number let alone one that matched the top/bottom of your 2 number range.

You can definitely stop reading here if you just want to get it working. Keep reading if you'd like to read how it works.

'The first line of code defines the function name (ScaleReturn, you can change this to whatever you like as long as the name does not contain spaces, special characters (_ is ok though), start with a number or the word is reversed by Excel for another use), if you were to change the name all other instances of ScaleReturn would need to be changed to the same thing. The first line also tells Excel what the arguments of the function are (In our example InputValue was C2 and LowerBound was I2:I22. InputValue and LowerBound are both variables.

Function ScaleReturn(InputValue, LowerBound As Range)

Dim Cell As Range 'Dim is short for dimension, we are telling excel that we want to use Cell as a variable with the type range (ie. A1, B2:B20, etc. As opposed to a different type of variable such as an integer or string.)

For Each Cell In LowerBound 'Here we are starting a loop, Everything after this line to the line reading: next cell, will run once for each worksheet cell in our range named LowerBound, ie runs once for I2, once for I3, etc. up to and including I22.

If InputValue >= Cell.Value And InputValue <= Cell.Offset(0, 1).Value Then 'This says if the value stored in the variable InputValue (Value in C2 for the example) is greater than or equal to the value of the cell I2 (For the first iteration of the loop, I3 for the next and so on) and if the value in C2 is less than or equal to the value in the cell one to the right (.Offset(0,1) means one cell to the right) then run the code between this line and the line End If

ScaleReturn = Cell.Offset(0, 2).Value 'We are storing the value found in the cell 2 to the right of cell in the current iteration of the loop (ie. The number we want out function to return), if C2 is within I2 and J2 this would be the cell 2 to the right of i2, so k2, this line will never run until we've met the condition of the If line above

End If 'this denotes the end of our if code

Next Cell 'takes us back to the beginning of the for each cell loop but for the next cell in the range (ex. if we just finished running the code for I2, start again but for I3)

If ScaleReturn = 0 Then 'Another if, I didn't want the function to return 0 if no matches for your 2 number ranges were met so we check if it's 0 and replace with "" (which is a blank cell) on the next line. You did not request this but I thought it would make more sense to return nothing than 0 if no matching 2 number range was found, thought it was possible one of the values you wanted to return could be 0, wanted to avoid any ambiguity there)

ScaleReturn = ""

End If 'close this if statement

End Function 'A UDF always ends with this line, lets Excel know when to stop running code, whatever is sitting in the variable ScaleReturn when Excel gets to this line is what the function is going to return on your worksheet.