Ok, there are two problems. The formula is wrong and the table is wrong.
First the table. You need descreet values. $0-$25 isn't a range to Excel, it's a text cell. You need to input descreet vaules. So change it to 0, 26, 46 for A16-A18. You need the smallest value for the range you want to represent for the formula to work.
Next the formula. You were trying to get it to look at the ranges, but VLOOKUP has a built in function that will return the value for a range. It assumes that the table has a range from cell AX
and that everything that is greater than or equal to AX
and less than AX+1
cooresponds to the value in col_index_num.
If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
So you should make your formula: =VLOOKUP(B4,$A$16:$B$18,2,TRUE)
This is how the formula/table works: It takes the value from B4 ($24) and compares it to the first column in the table from A16 to B18. Since the range_lookup value is TRUE, it sees that 0 is less than 24, so it returns the value in column 2 of the table: 0.35. Setting it to FALSE would give you a #N/A because there is no value of 24. If the value of B4 was 26, you would get a return of 0.45 since the value in A17 is less than or equal the lookup_value.
Just format the cells to display percentage and you'll get the display you want.
Edited by fructose, 01 August 2006 - 01:30 PM.