Forum Discussion
Selecting a value by determining if a number lands between two values on a scale
You may use formula like
=INDEX($K$2:$K$40,MATCH(1,INDEX(($D2>=$I$2:$I$40)*($D2<=$J$2:$J$40),0),0))
Please see attached
Hi there! Thanks for giving this a whirl. This may be the trick, unfortunately, I'm on an elementary level of excel, so I'm not able to follow your directions with any results. But, given my feeble understanding of the process below, I'm not sure how the upper range of the scale would be taken into consideration.
I've hopefully attached a scrubbed version of the sheet I'm working on.
In the attached example, any value in column D between 26,505 and 29,295 would be assigned a 100. 29,295 to 32,025 would be a 200.
The second sheet on the attached workbook shows the goal of the formula.
The goal is to weigh the values in column D against the range (I to J) and assign the proposed value in column C (mistyped as B in my initial post)
Thank you again
If this the intended result? (Attached). Either I'm not completely understanding what you are doing or you've got a few typos on the sheet you did manually) I've got to get off the computer for the day but I can explain how this function works in depth at a later point, Do you understand the concept of User Defined Functions (No slight intended, you said your knowledge is elementary), the code I sent needs to be inserted into a VBA module (I can give you a run down later, or just google how to use a User Defined Function in Excel).
Yes, it was likely typos as I tried to do it quickly. This is also the reason I wanted to automate it and remove operator error.
This is fantastic. At first glance this looks like what I needed. I will do some research on User Defined Functions and attempt to replicate your result.
Thank you!
I'll post an explanation later tonight.
Thank you again for your guidance! Leveraging User Defined Functions in the future will certainly be a valuable asset while trying to refine this old spreadsheet into a more useful tool. I will have to experiment if there are any adverse conditions created by converting the main workbook into a macro enabled version. Currently the macros I created through the record macro function run with buttons added to the quick access - I was unaware of the different version of the spreadsheets. Being able to build them into the book itself seems like something that might be a good quality of life change.
Another solution that was recommended was use of the lookup function. In this case this function copied down:
=LOOKUP(D2,$I$2:$I$22,$K$2:$K$22)
I sincerely appreciate your guidance!