Vlookup-table array runs?

[chili]

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,

 

[ND Pard]

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.

 

[Gord Dibben]

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

Insert>Name>Define name of mytable

Refers to =Sheet1!$E$2:$F$10

=VLOOKUP(A2,mytable,2,FALSE)


Gord Dibben MS Excel MVP