Formula Help!!!!!!

I am struggling to find a formula that can lookup a range of numbers and autopopulate the correct answer.

For Example:

If someone has a rate between 67% and 69% they reeive a score of 24. I need to be able to enter 68% in a cell and have a formula that knows 68% is in between 67% and 69%.

Currently my data table is set up with 67% in cell B5, 69% in cell C5 and the score of 24 in cell D5. Do I need to set it up differently? Please help.

I have attached an example. THe PIR tab is where I inout data and the legend is where I need to pull it from

AttachmentSize
example.xlsx20.04 KB
Nick's picture

have you tried VLOOKUP with

have you tried VLOOKUP with the last argument = TRUE (for approximate match)

yes and it does not work

yes and it does not work

Nick's picture

sort your percentage data

sort your percentage data ascending, and it will work

Thank You I figured that out

Thank You I figured that out :)