Home » Forums » Open Source Learning Center » Question and Answer

Lookup - result is in a table of ranges, must return the next highest percentage in table

Submitted by cbruce on 12 December, 2012 - 03:05

I have a spreadsheet with a range of numbers with corresponding percentages. Lookup needs to check the range and return the correct percentage. Tried LOOKUP, INDEX/MATCH and more and still cant get the results I need.

Qty in ea or UOM Discount %
275-400 0.50%
401-600 1.00%
601-1000 1.50%
1001-1500 2.00%
1501-1800 2.25%
1801-2000 3.00%
2000-3500 3.75%
3501-5000 4.00%
5001-7500 5.00%
7501+ 6.00%

Example: Lookup Value is 599, correct result would be 1.00%

Can anyone help?

‹ Forgot personal.xls password Inserting multiple images from a unique URL to a word document via VBA ›
»

The problem is your data

Submitted by AndyLitch on 12 December, 2012 - 05:56.

The problem is your data table.. You have the range of values in a single column eg 275-400.. These need to be seperated into 2 columns and Vlookup will work fine...
275-400 may look like a range of values to you and I but Excel will treat it as text.

»

Thank you - I know that but

Submitted by cbruce on 13 December, 2012 - 01:18.

Thank you - I know that but for some reason it just didn't seem right.

»