Vlookup syntax
 

This is doubtless very, very simple but I've been staring at it for hours!

I have a spreadsheet with a series of unique ID numbers in column 1 of sheet
1. I have a list of people with ID number and name in sheet 2. I want to
lookup the relevant name for each ID and copy it into column 2 on the first
sheet.

I've used:

=VLOOKUP($A4,'Report data'!$A$1:$K$500, 9,FALSE)

This has worked fine before on a different spreadsheet (I was testing out
vlookup) but now returns #N/A.

I've tried omitting the $ in the ranges, that makes no difference.

Can anyone help please?

Sue

Vlookup syntax
 

Are your data to be returned by VLOOKUP really in column I (the 9th column
relative to A)? If yes, why is necessary to define A:K search range?
Regards,
Stefi


€žSueJB€� ezt Ã*rta:

This is doubtless very, very simple but I've been staring at it for hours!

I have a spreadsheet with a series of unique ID numbers in column 1 of sheet
1. I have a list of people with ID number and name in sheet 2. I want to
lookup the relevant name for each ID and copy it into column 2 on the first
sheet.

I've used:

=VLOOKUP($A4,'Report data'!$A$1:$K$500, 9,FALSE)

This has worked fine before on a different spreadsheet (I was testing out
vlookup) but now returns #N/A.

I've tried omitting the $ in the ranges, that makes no difference.

Can anyone help please?

Sue

Vlookup syntax
 

Helo Stefi

Yes, the names to be returned are in column I; I defined the range because
that's what Excel help said! Is it not needed?

Sue

"Stefi" wrote:

Are your data to be returned by VLOOKUP really in column I (the 9th column
relative to A)? If yes, why is necessary to define A:K search range?
Regards,
Stefi


€žSueJB€� ezt Ã*rta:

This is doubtless very, very simple but I've been staring at it for hours!

I have a spreadsheet with a series of unique ID numbers in column 1 of sheet
1. I have a list of people with ID number and name in sheet 2. I want to
lookup the relevant name for each ID and copy it into column 2 on the first
sheet.

I've used:

=VLOOKUP($A4,'Report data'!$A$1:$K$500, 9,FALSE)

This has worked fine before on a different spreadsheet (I was testing out
vlookup) but now returns #N/A.

I've tried omitting the $ in the ranges, that makes no difference.

Can anyone help please?

Sue

Vlookup syntax
 

Best I can tell you is that your formula is correct. Common problems
with an obviosuly correct formula not working... make sure the "ID" that
you are looking up stored as an excel recognized number...sometimes when
data comes from an external database it is not stored as such. Also,
make sure you have no columns hidden, ensuring that you are in fact
returning the 9th column. If all this is good, try copying the data
sheets and Paste Special Values to a new workbook and retry your
formula. (Start fresh). But I can guarantee you that your formula is
correct, therefore the data or the layout of data is your prob. (;
-Mike

ooh.. also make sure your Report data sheet is referenced exactly as it
is in your formula. In otherwords make sure your sheetname is "Report
data"


*** Sent via Developersdex http://www.developersdex.com ***

Vlookup syntax
 

if you'd like me to take a look at it, send it over

--


Gary


"SueJB" wrote in message
...
Helo Stefi

Yes, the names to be returned are in column I; I defined the range
because
that's what Excel help said! Is it not needed?

Sue

"Stefi" wrote:

Are your data to be returned by VLOOKUP really in column I (the 9th
column
relative to A)? If yes, why is necessary to define A:K search range?
Regards,
Stefi


"SueJB" ezt írta:

This is doubtless very, very simple but I've been staring at it for
hours!

I have a spreadsheet with a series of unique ID numbers in column 1 of
sheet
1. I have a list of people with ID number and name in sheet 2. I want
to
lookup the relevant name for each ID and copy it into column 2 on the
first
sheet.

I've used:

=VLOOKUP($A4,'Report data'!$A$1:$K$500, 9,FALSE)

This has worked fine before on a different spreadsheet (I was testing
out
vlookup) but now returns #N/A.

I've tried omitting the $ in the ranges, that makes no difference.

Can anyone help please?

Sue

Vlookup syntax
 

Michael,

Many thanks for this. If I start fresh, as you suggest, it works.
Something is obviously awry with the spreadsheet!

Best wishes
Sue

"Michael Smith" wrote:

Best I can tell you is that your formula is correct. Common problems
with an obviosuly correct formula not working... make sure the "ID" that
you are looking up stored as an excel recognized number...sometimes when
data comes from an external database it is not stored as such. Also,
make sure you have no columns hidden, ensuring that you are in fact
returning the 9th column. If all this is good, try copying the data
sheets and Paste Special Values to a new workbook and retry your
formula. (Start fresh). But I can guarantee you that your formula is
correct, therefore the data or the layout of data is your prob. (;
-Mike

ooh.. also make sure your Report data sheet is referenced exactly as it
is in your formula. In otherwords make sure your sheetname is "Report
data"


*** Sent via Developersdex http://www.developersdex.com ***

Vlookup syntax
 

Gary

Many thanks for the offer, but copying to a new spreadsheet seems to have
solved it.

Best wishes
Sue

"Gary Keramidas" wrote:

if you'd like me to take a look at it, send it over

--


Gary


"SueJB" wrote in message
...
Helo Stefi

Yes, the names to be returned are in column I; I defined the range
because
that's what Excel help said! Is it not needed?

Sue

"Stefi" wrote:

Are your data to be returned by VLOOKUP really in column I (the 9th
column
relative to A)? If yes, why is necessary to define A:K search range?
Regards,
Stefi


"SueJB" ezt Ã*rta:

This is doubtless very, very simple but I've been staring at it for
hours!

I have a spreadsheet with a series of unique ID numbers in column 1 of
sheet
1. I have a list of people with ID number and name in sheet 2. I want
to
lookup the relevant name for each ID and copy it into column 2 on the
first
sheet.

I've used:

=VLOOKUP($A4,'Report data'!$A$1:$K$500, 9,FALSE)

This has worked fine before on a different spreadsheet (I was testing
out
vlookup) but now returns #N/A.

I've tried omitting the $ in the ranges, that makes no difference.

Can anyone help please?

Sue

Vlookup syntax
 

great, glad you got it working. and it's nice for you to know what you were
doing was right and something else was messed up.

--


Gary


"SueJB" wrote in message
...
Gary

Many thanks for the offer, but copying to a new spreadsheet seems to have
solved it.

Best wishes
Sue

"Gary Keramidas" wrote:

if you'd like me to take a look at it, send it over

--


Gary


"SueJB" wrote in message
...
Helo Stefi

Yes, the names to be returned are in column I; I defined the range
because
that's what Excel help said! Is it not needed?

Sue

"Stefi" wrote:

Are your data to be returned by VLOOKUP really in column I (the 9th
column
relative to A)? If yes, why is necessary to define A:K search range?
Regards,
Stefi


"SueJB" ezt írta:

This is doubtless very, very simple but I've been staring at it for
hours!

I have a spreadsheet with a series of unique ID numbers in column 1
of
sheet
1. I have a list of people with ID number and name in sheet 2. I
want
to
lookup the relevant name for each ID and copy it into column 2 on
the
first
sheet.

I've used:

=VLOOKUP($A4,'Report data'!$A$1:$K$500, 9,FALSE)

This has worked fine before on a different spreadsheet (I was
testing
out
vlookup) but now returns #N/A.

I've tried omitting the $ in the ranges, that makes no difference.

Can anyone help please?

Sue