Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Error | Excel Worksheet Functions | |||
vlookup error... | Excel Worksheet Functions | |||
vlookup error | Excel Discussion (Misc queries) | |||
vlookup error | Excel Worksheet Functions | |||
vlookup #N/A error | Excel Discussion (Misc queries) |