Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup-table array runs?
Hi All,
I have been struggling with this problem for several days as I need to run it to add in for street codes and I learnt vlookup from other examples given around. I used vlookup on 1st cell A2. It works Ok. After that, I used the same formula, copy & paste to the rest of the cells. It comes out N/A error. Traced through, and turned out the formula ( lookup highlighted blue, table array is green) the highlight green area which table array supposed to be in has run/gone down to another cell ( from E1 to E2). In all, how do I successfully duplicate & apply vlookup to other cells too? or can I "fixed" the highlighted table array not to run? I have tried =VLOOKUP(A6,$E6:$F10,2) putting $ sign on it, but it is still not correct. Thank you very much for replying, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup-table array runs?
have your tried making the rows in your lookup source absolute?
IE, instead of: =VLOOKUP(A6,$E6:$F10,2) try: =VLOOKUP(A6,$E$6:$F$10,2) Good Luck. "chili" wrote: Hi All, I have been struggling with this problem for several days as I need to run it to add in for street codes and I learnt vlookup from other examples given around. I used vlookup on 1st cell A2. It works Ok. After that, I used the same formula, copy & paste to the rest of the cells. It comes out N/A error. Traced through, and turned out the formula ( lookup highlighted blue, table array is green) the highlight green area which table array supposed to be in has run/gone down to another cell ( from E1 to E2). In all, how do I successfully duplicate & apply vlookup to other cells too? or can I "fixed" the highlighted table array not to run? I have tried =VLOOKUP(A6,$E6:$F10,2) putting $ sign on it, but it is still not correct. Thank you very much for replying, -- chili |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup-table array runs?
=VLOOKUP(A2,$E$2:$F$10,2)
I would add the 4th argument of false so Excel looks for an exact match. =VLOOKUP(A2,$E$2:$F$10,2,FALSE) You could, as an alternative, give the Lookup table a defined name. InsertNameDefine name of mytable Refers to =Sheet1!$E$2:$F$10 =VLOOKUP(A2,mytable,2,FALSE) Gord Dibben MS Excel MVP On Thu, 2 Oct 2008 17:31:38 +0100, chili wrote: Hi All, I have been struggling with this problem for several days as I need to run it to add in for street codes and I learnt vlookup from other examples given around. I used vlookup on 1st cell A2. It works Ok. After that, I used the same formula, copy & paste to the rest of the cells. It comes out N/A error. Traced through, and turned out the formula ( lookup highlighted blue, table array is green) the highlight green area which table array supposed to be in has run/gone down to another cell ( from E1 to E2). In all, how do I successfully duplicate & apply vlookup to other cells too? or can I "fixed" the highlighted table array not to run? I have tried =VLOOKUP(A6,$E6:$F10,2) putting $ sign on it, but it is still not correct. Thank you very much for replying, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & non-contiguous table array | Excel Worksheet Functions | |||
array table and vlookup | Excel Worksheet Functions | |||
vlookup - can't pick up table array | Excel Worksheet Functions | |||
i want to do a vlookup but i want the table array to be changing | Excel Worksheet Functions | |||
VLOOKUP - 3 Table Array | Excel Worksheet Functions |