How to use ranges of numbers for assigning specified values

[kian82]

Hi,

I need to assign a specific number of required hours for each range of numbers.

Basically, my Excel looks like this:
Column A:
0-100
101-200
201-300
etc.

And next to column A, I wrote down values such as 1 to denote the number of hours required if the lookup value is between 0-100 etc.

Now I know about a possible workaround by using the vlookup function, but for this I would need to create kind of a mapping table, ideally on a separate tab in order to avoid clutter, that will list all single numbers that are relevant for the range (in fact 10.000 is the relevant maximum range, thus I would need to use 10.000 excel line entries). I want to avoid that and instead go with numeric ranges as described above.

Any idea how I could realize that with an excel function? VBA would not be my cup of tea.

Regards
Kian

[mike7952]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[etaf]

you dont need to list all the numbers just the range
0
100
200
300

etc

then use a vlookup with a TRUE and it will look at the lowest nearest value
so if against 0 you had 1 and next to 100 you had 2

anything between 0 and 99 would return the 1
anything between 100 and 199 would return a 2

also Excel will series a list
so you should be able to just copy down - still would be 100 rows
10,000 divided by 100 as they are multiples of 100

[kian82]

Understood. Please find my request as requested attached

[etaf]

As suggested
change the table slightly and use a TRUE vlookup

[mike7952]

See attached workbook

[kian82]

guys, you are awesome. thanks for helping! the solution works