[qwer]

I am using this formula =VLOOKUP(B4,$A$16:$B$18,2,FALSE ) for VLOOKUP

I have a VLOOKUP table that starts in A16 with $0-$25.$26-$45,$46+ going down and in B16 i have 35%,45%,35% going down. When ever i put it in the formula i get #NA. The value in B4 is $24.00 and i put the formula in cell C5
Please help me thnx

Edited by qwer, 01 August 2006 - 08:18 AM.

[Advertisements]

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 to AX+1 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.

[fructose]

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 to AX+1 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.