vlookup error

M

Matin D HJE

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
 
S

Sheeloo

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)
 
M

Matin D HJE

Sheeloo said:
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)
 
M

Matin D HJE

Sheeloo said:
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)


:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top