Look Up table not pulling correct figure

Hi All,

I wonder if anyone can assist with an issue I have. I am currently trying to calculate a figure (F3) using the figure in (C2) and (C3) by then looking at a table on a separate sheet called 'base rates' to get the figure. I am amending an old formula and previously the list in C2 started at 500 and went up in increments of 500. I have re labeled the table in 'base rates' and if I continue to populate C2 with the 500 (for example) it does give the correct answer, however I want the numbers entered into (C2) to now run from 24-50 as opposed to 500-13000 (in 500 increments).

The problem is two fold. The first is that when I put any number under 500 into C2 it returns a value of 2, in addition to get it to return the correct answer, I have to put in the larger figure and I cant work out where it is still looking to the 500 increments when I have changed it. (24 = 1 in the table but I have to put 500 in to get this result, 25 = 2 but I have to put in 1000 etc...)

=IF(C3>500,0,HLOOKUP(C3,'Base rates'!B4:J30,Calculator!D3/500+1,FALSE))

I think its the bit in blue causing the issues.

Many Thanks

Just looking at this, I can see an issue. D3/500 will most likely come out as a decimal. It needs to be an integer. What are you trying to do with this formula? Could you post a sample spreadsheet? I would help a lot.

The part you have in blue needs to be how many columns to go across.

Thanks dflak

I managed to address the error. The only issue I now need to address is how to get C2 to display I5 (26) and not the number of the line that the information is on. Any ideas?

Please see attached

Values.jpg