vlookup error
 

I am using Excel 2003. Using the following data table (part ncluded) I am
using Vlookup to look up a corrction value for 'A' in cell b3 based on the
table relating to 'A'-0.1. Formula used for cell b4
=VLOOKUP(B3-0.1,A7:AB20,2,FALSE)
Par t table : A7 = 0.00 and increments by 0.01 to 0.13 in A20. Corresponding
values in column B are B7=0.00, b8=0.02, b9=0.04,b10=0.06, b11=0.07,
b12=0.08, b13=0.09, b14=0.11.b15=.012,b6=0.13,b17=0.14, b18=0.15, b19=0.17 &
b20=0.18
The formula seems to work for some values of 'A' in the range but returns
#N/a for others. Cell formats all set to number.
Advice welcomed please

vlookup error
 

How are you copying the formula to other cells?
=VLOOKUP(B3-0.1,A7:AB20,2,FALSE)
will change to
=VLOOKUP(B4-0.1,A8:AB21,2,FALSE)
when copied down...

Use
=VLOOKUP(B3-0.1,$A$7:$AB$20,2,FALSE)
Also since you are picking up values from column B you can also use
=VLOOKUP(B3-0.1,$A$7:$B$20,2,FALSE)


"Matin D HJE" wrote:

I am using Excel 2003. Using the following data table (part ncluded) I am
using Vlookup to look up a corrction value for 'A' in cell b3 based on the
table relating to 'A'-0.1. Formula used for cell b4
=VLOOKUP(B3-0.1,A7:AB20,2,FALSE)
Par t table : A7 = 0.00 and increments by 0.01 to 0.13 in A20. Corresponding
values in column B are B7=0.00, b8=0.02, b9=0.04,b10=0.06, b11=0.07,
b12=0.08, b13=0.09, b14=0.11.b15=.012,b6=0.13,b17=0.14, b18=0.15, b19=0.17 &
b20=0.18
The formula seems to work for some values of 'A' in the range but returns
#N/a for others. Cell formats all set to number.
Advice welcomed please

vlookup error
 

"Sheeloo" wrote:

How are you copying the formula to other cells?
=VLOOKUP(B3-0.1,A7:AB20,2,FALSE)
will change to
=VLOOKUP(B4-0.1,A8:AB21,2,FALSE)
when copied down...

Use
=VLOOKUP(B3-0.1,$A$7:$AB$20,2,FALSE)
Also since you are picking up values from column B you can also use
=VLOOKUP(B3-0.1,$A$7:$B$20,2,FALSE)


"Matin D HJE" wrote:

I am using Excel 2003. Using the following data table (part ncluded) I am
using Vlookup to look up a corrction value for 'A' in cell b3 based on the
table relating to 'A'-0.1. Formula used for cell b4
=VLOOKUP(B3-0.1,A7:AB20,2,FALSE)
Par t table : A7 = 0.00 and increments by 0.01 to 0.13 in A20. Corresponding
values in column B are B7=0.00, b8=0.02, b9=0.04,b10=0.06, b11=0.07,
b12=0.08, b13=0.09, b14=0.11.b15=.012,b6=0.13,b17=0.14, b18=0.15, b19=0.17 &
b20=0.18
The formula seems to work for some values of 'A' in the range but returns
#N/a for others. Cell formats all set to number.
Advice welcomed please

vlookup error
 

"Sheeloo" wrote:

How are you copying the formula to other cells?
=VLOOKUP(B3-0.1,A7:AB20,2,FALSE)
will change to
=VLOOKUP(B4-0.1,A8:AB21,2,FALSE)
when copied down...

Use
=VLOOKUP(B3-0.1,$A$7:$AB$20,2,FALSE)
Also since you are picking up values from column B you can also use
=VLOOKUP(B3-0.1,$A$7:$B$20,2,FALSE)


"Matin D HJE" wrote:

Thank you for the reply but I am not copying the cells down. A single look
uo cell is being used as part of a scientific 'calculator' for extracting
correction data from the table part of which I included in the original
enquiry. This look up cell is then accessed by a further calculation which
exists in another worksheet within the workbook.