Formula needed to identify number within a range of numbers

**easglos** · 07-26-2012, 09:07 AM

Hi

I was hoping for some help in identifying whether what I want to do can be done...

I have a cost and then I have a list of scores allocated to each cost. I have identified the cost and score in a table and then done sumif to look up the score of the cost identified.

However this is only working for the round number costs that I have allocated. I.e. £650k is 98 points and £700k is 100 points but there could be a cost of £699,999k. is there anyway of making a formula to identify or round the £699 up to the £700k score?

Many thanks to anyone who can help!

**Ron Coderre** · 07-26-2012, 09:15 AM

Try this...
• Sort your Price/Score list in ascending order by Price
Then...
D6: =IF(ISNUMBER(C6),VLOOKUP(C6,$G$5:$H$27,2,1),"")

Copy D6 down through D10

Regarding VLOOKUP(C6,$G$5:$H$27,2,1) in that formula:
The 4th argument, the 1, indicates that approximate matching is engaged. When the list items are in ascending order, it will match on the highest item that is less than or equal to the item to be found.

Is that something you can work with?