V LOOKUP

[Mel]

Hi,

I am having a problem with a VLOOK UP table.

I am looking up a URN in one table and asking it to return a value which
appears to the right of it, (It also happens to be a date). There is no
problem with the formula as it worked once, however if I drag it down it
returns "N/A". I have checked to see that where there is a value of N/A (I
have checked that the record appears in both databases), so it should be
returning the date. Can anyone help, I am not quite sure where I am going
wrong?

 

[Dave R.]

With the vlookup, you should surround the location of the lookup table with
$, e.g.

$A$1:$B$600

It's possible now that you have
A1:B600

....so when you drag it down you will have things like

A25:B624

in which case it wouldn't find anything until A25.

 

[Frank Kabel]

Hi
please post your formula. Probably you just have to use an absolute
cell reference.
e.g. instead of
=VLOOKUP(E1,A1:B100,2,0)
use
=VLOOKUP(E1,$A$1:$B$100,2,0)

 

[Chris]

Hi all,

I've seen Mel's formula.
She has =vlookup(A1,$A1:$B450,2,false)

Does that help?

 

[Frank Kabel]

Hi
are you sure this is the exact formula as you have A1 both as lookup
value and in your search range. This formula will always return cell B1

the formula probably looks like
=vlookup(A1,'other sheetname'!$A1:$B450,2,false)

if yes she may change it to
=vlookup(A1,'other sheetname'!$A$1:$B$450,2,false)