Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers for
the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

I get a N/A as the result . Please tell me what I am doing wrong here?
Is there any other way to do it. I arranged the data in ascending order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

Maybe try put a $ sign in front of the first A2

ie.
=VLOOKUP($A2,Detail!$A$2:$F$29497,5,FALSE)




navneetjn Wrote:
Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers for
the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

I get a N/A as the result . Please tell me what I am doing wrong here?
Is there any other way to do it. I arranged the data in ascending order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388108

That didnt help!!
Thanks anyway. Does it have to do anything with the fact that I am
using Excel 97.

Morrigan Wrote:
Maybe try put a $ sign in front of the first A2

ie.
=VLOOKUP($A2,Detail!$A$2:$F$29497,5,FALSE)


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

works fine for me. Are you sure that some of your numbers aren't text cells?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"navneetjn" wrote
in message ...

Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers for
the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

I get a N/A as the result . Please tell me what I am doing wrong here?
Is there any other way to do it. I arranged the data in ascending order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

I've run into the problem which Bob is suggesting. Try using an unused cell
in header and type =A2=detail!A2(where you know the numbers should match).
If this says false, one of your columns will have to be converted to
numbers(from text). There are numerous ways to do this. A convenient one
I've found is typing 1 in a random cell, and click on it and copy; and then
highlight your range and paste special and toggle multiply[multiplies the
range by one converting it to a number]

hth,
Dave

"navneetjn" wrote:


Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers for
the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

I get a N/A as the result . Please tell me what I am doing wrong here?
Is there any other way to do it. I arranged the data in ascending order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

The formula does not seem wrong to me. #N/A means excel does not find
the match. Also check column A on both sheets and see if they are
entered differently in format.

One example is when someone entered the number with ' in front, ie.

'162955

Even you retype it as 162955 after, Excel still does not read it
properly somehow. At least that's what it is in Excel 97.




navneetjn Wrote:
That didnt help!!
Thanks anyway. Does it have to do anything with the fact that I am
using Excel 97.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388108

That did work. Amazing!!!
I just went to the worksheet "Detail" Column A and retyped the number
and voila it showed me the correct response.
Now the problem is why does it happen this way?
And since I copied the data from an Access database now I will have to
go and retype 1200 entries. Wow thats too much for a day!!! It just
defeated my original purpose of saving time.
Any way thanks a lot, if you figure out why excel 97 does that do let
me know.
;) Navneet ;)

Morrigan Wrote:
The formula does not seem wrong to me. #N/A means excel does not find
the match. Also check column A on both sheets and see if they are
entered differently in format.

One example is when someone entered the number with ' in front, ie.

'162955

Even you retype it as 162955 after, Excel still does not read it
properly somehow. At least that's what it is in Excel 97.


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

I am not sure why but try to use paste values instead of paste.

If all the values in column A are numbers, you can try this:

Make a helper column and do =value(columnA), it should return the exact
number. Now replace copy and paste this.



navneetjn Wrote:
That did work. Amazing!!!
I just went to the worksheet "Detail" Column A and retyped the number
and voila it showed me the correct response.
Now the problem is why does it happen this way?
And since I copied the data from an Access database now I will have to
go and retype 1200 entries. Wow thats too much for a day!!! It just
defeated my original purpose of saving time.
Any way thanks a lot, if you figure out why excel 97 does that do let
me know.
;) Navneet ;)


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388108

Tried copying and pasting as values, made a dummy column and tried to
copy from that. even tried to copy from the original access databse,
tried changing the format to almost all possible configurations.
One thing did work though !!!!!
*Press "F2" then press "enter" 1200 times*

Anyways thanks for your help.
Now please help me in getting a multiple value from sheet "detail"
corresponding to a single value of A2 in sheet "header"
Navneet


navneetjn Wrote:
That did work. Amazing!!!
I just went to the worksheet "Detail" Column A and retyped the number
and voila it showed me the correct response.

I copied the data from an Access database now I will have to go and
retype 1200 entries. Wow thats too much for a day!!! It just defeated
my original purpose of saving time.

Any ways thanks a lot, if you figure out why excel 97 does that do let
me know.

NOW NEW PROBLEM!!!
The second sheet column 5 I am looking up has different enteries for a
single value of A2. How can I display all of those values?
e.g.
Sheet "Header" column A
169255
169226
169247
169248
Sheet "Detail" column A and E
--- A ----------- E --
169255 U409A
169255 J237A
169226 J103A
169226 D303A
169226 6108B
169226 1102A
169248 1102A
169248 7862A

;) Navneet ;)


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

If all your data are numbers it should work with a dummy column. The
catch is you do NOT use paste values. You want to overwrite the
internal formatting.

If all your data are text, you probably did not have the problem you
have.



navneetjn Wrote:
Tried copying and pasting as values, made a dummy column and tried to
copy from that. even tried to copy from the original access databse,
tried changing the format to almost all possible configurations.
One thing did work though !!!!!
*Press "F2" then press "enter" 1200 times*

Anyways thanks for your help.
Now please help me in getting a multiple value from sheet "detail"
corresponding to a single value of A2 in sheet "header"
Navneet


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388108

Dave this one worked well with my case, I typed into a column =1 and
then selected a empty column and special pasted values with addition
selected. This pasted the 1 in all the rows with the formula =0+(1). In
another blank column I wrote the formula =A2*K2 where K as the column
where I copied the 1's, then I sort of copied the formula all
throughout the new column by selecting the new column and special
pasting only formula. I got rid of the extra 0 & 1 after the data by
going to the end of data and selecting the remaining data
by[ctrl]+[shift]+[end] and then pressing[delete].
I referenced the vlookup to the new column and hid the other columns.
All this shows that Excel 97 does not like the format of the pasted
cells.
Will try to work the same thing in Excel 2000 and 2003 to see what
happens.
Thanks
Navneet

Dave Breitenbach Wrote:
I've run into the problem which Bob is suggesting. Try using an unused
cell
in header and type =A2=detail!A2(where you know the numbers should
match).
If this says false, one of your columns will have to be converted to
numbers(from text). There are numerous ways to do this. A convenient
one
I've found is typing 1 in a random cell, and click on it and copy; and
then
highlight your range and paste special and toggle multiply[multiplies
the
range by one converting it to a number]

hth,
Dave

"navneetjn" wrote:


Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and
detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers for
the data.

I used the following formula in the "header" sheet at cell location
I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to
show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

I get a N/A as the result . Please tell me what I am doing wrong
here?
Is there any other way to do it. I arranged the data in ascending
order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
navneetjn

------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread:
http://www.excelforum.com/showthread...hreadid=388108




--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

I don't believe that the version of XL has anything to do with your problem!

If some of your numbers are text that appear as numbers, and others are true
numbers, all versions of XL will reject them as a "match".

There are several ways to accomplish a "bulk" revision without having to
resort to manual corrections.

Try this on both columns of numbers (you might have "bad" numbers in both):

Select the column of "suspect" numbers, then:
<Data <Text To Columns <Finish

Did that help?

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"navneetjn" wrote
in message ...

Dave this one worked well with my case, I typed into a column =1 and
then selected a empty column and special pasted values with addition
selected. This pasted the 1 in all the rows with the formula =0+(1). In
another blank column I wrote the formula =A2*K2 where K as the column
where I copied the 1's, then I sort of copied the formula all
throughout the new column by selecting the new column and special
pasting only formula. I got rid of the extra 0 & 1 after the data by
going to the end of data and selecting the remaining data
by[ctrl]+[shift]+[end] and then pressing[delete].
I referenced the vlookup to the new column and hid the other columns.
All this shows that Excel 97 does not like the format of the pasted
cells.
Will try to work the same thing in Excel 2000 and 2003 to see what
happens.
Thanks
Navneet

Dave Breitenbach Wrote:
I've run into the problem which Bob is suggesting. Try using an unused
cell
in header and type =A2=detail!A2(where you know the numbers should
match).
If this says false, one of your columns will have to be converted to
numbers(from text). There are numerous ways to do this. A convenient
one
I've found is typing 1 in a random cell, and click on it and copy; and
then
highlight your range and paste special and toggle multiply[multiplies
the
range by one converting it to a number]

hth,
Dave

"navneetjn" wrote:


Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and
detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers for
the data.

I used the following formula in the "header" sheet at cell location
I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to
show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

I get a N/A as the result . Please tell me what I am doing wrong
here?
Is there any other way to do it. I arranged the data in ascending
order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
navneetjn

------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread:
http://www.excelforum.com/showthread...hreadid=388108




--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

That was awesome!!!
This one did the trick and in just one shot.
Thanks to all the people who have helped me out and saved my day.
Navneet

RagDyer Wrote:
I don't believe that the version of XL has anything to do with your
problem!

If some of your numbers are text that appear as numbers, and others are
true
numbers, all versions of XL will reject them as a "match".

There are several ways to accomplish a "bulk" revision without having
to
resort to manual corrections.

Try this on both columns of numbers (you might have "bad" numbers in
both):

Select the column of "suspect" numbers, then:
<Data <Text To Columns <Finish

Did that help?

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"navneetjn"
wrote
in message
...

Dave this one worked well with my case, I typed into a column =1 and
then selected a empty column and special pasted values with addition
selected. This pasted the 1 in all the rows with the formula =0+(1).
In
another blank column I wrote the formula =A2*K2 where K as the
column
where I copied the 1's, then I sort of copied the formula all
throughout the new column by selecting the new column and special
pasting only formula. I got rid of the extra 0 & 1 after the data by
going to the end of data and selecting the remaining data
by[ctrl]+[shift]+[end] and then pressing[delete].
I referenced the vlookup to the new column and hid the other
columns.
All this shows that Excel 97 does not like the format of the pasted
cells.
Will try to work the same thing in Excel 2000 and 2003 to see what
happens.
Thanks
Navneet

Dave Breitenbach Wrote:
I've run into the problem which Bob is suggesting. Try using an
unused
cell
in header and type =A2=detail!A2(where you know the numbers should
match).
If this says false, one of your columns will have to be converted
to
numbers(from text). There are numerous ways to do this. A
convenient
one
I've found is typing 1 in a random cell, and click on it and copy;
and
then
highlight your range and paste special and toggle
multiply[multiplies
the
range by one converting it to a number]

hth,
Dave

"navneetjn" wrote:


Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and
detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers
for
the data.

I used the following formula in the "header" sheet at cell
location
I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header"
to
show
the data corresponding to A2 in the range $A$1:$F29497 for the
5th
column.

I get a N/A as the result . Please tell me what I am doing wrong
here?
Is there any other way to do it. I arranged the data in
ascending
order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
navneetjn


------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread:
http://www.excelforum.com/showthread...hreadid=388108




--
navneetjn

------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread:
http://www.excelforum.com/showthread...hreadid=388108



--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108

Appreciate the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"navneetjn" wrote
in message ...

That was awesome!!!
This one did the trick and in just one shot.
Thanks to all the people who have helped me out and saved my day.
Navneet

RagDyer Wrote:
I don't believe that the version of XL has anything to do with your
problem!

If some of your numbers are text that appear as numbers, and others are
true
numbers, all versions of XL will reject them as a "match".

There are several ways to accomplish a "bulk" revision without having
to
resort to manual corrections.

Try this on both columns of numbers (you might have "bad" numbers in
both):

Select the column of "suspect" numbers, then:
<Data <Text To Columns <Finish

Did that help?

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"navneetjn"
wrote
in message
...

Dave this one worked well with my case, I typed into a column =1 and
then selected a empty column and special pasted values with addition
selected. This pasted the 1 in all the rows with the formula =0+(1).
In
another blank column I wrote the formula =A2*K2 where K as the
column
where I copied the 1's, then I sort of copied the formula all
throughout the new column by selecting the new column and special
pasting only formula. I got rid of the extra 0 & 1 after the data by
going to the end of data and selecting the remaining data
by[ctrl]+[shift]+[end] and then pressing[delete].
I referenced the vlookup to the new column and hid the other
columns.
All this shows that Excel 97 does not like the format of the pasted
cells.
Will try to work the same thing in Excel 2000 and 2003 to see what
happens.
Thanks
Navneet

Dave Breitenbach Wrote:
I've run into the problem which Bob is suggesting. Try using an
unused
cell
in header and type =A2=detail!A2(where you know the numbers should
match).
If this says false, one of your columns will have to be converted
to
numbers(from text). There are numerous ways to do this. A
convenient
one
I've found is typing 1 in a random cell, and click on it and copy;
and
then
highlight your range and paste special and toggle
multiply[multiplies
the
range by one converting it to a number]

hth,
Dave

"navneetjn" wrote:


Hi,

I am new to Vlookup and tried using it in the following format

Sheet1 is named "header" Sheet2 is named "detail"
The sheet "header" has 8 columns, 1200 rows (all unique), and
detail
has 6 columns and 29000 rows (with duplicates).
The records in sheet "header" A2:A1200 are unique
e.g.
169255
169226
169247
169248
169249
The records in sheet "detail" A2:A29497 may be duplicate
e.g.
169255
169255
169247
169247
169247
169248
169248
169248
169248
169249

Data in each sheet starts in Row 2, with row 1 being the headers
for
the data.

I used the following formula in the "header" sheet at cell
location
I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header"
to
show
the data corresponding to A2 in the range $A$1:$F29497 for the
5th
column.

I get a N/A as the result . Please tell me what I am doing wrong
here?
Is there any other way to do it. I arranged the data in
ascending
order
on both worksheets, but it still shows N/A.

Thanks in advance
Navneet


--
navneetjn


------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread:
http://www.excelforum.com/showthread...hreadid=388108




--
navneetjn

------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread:
http://www.excelforum.com/showthread...hreadid=388108



--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388108