Problem in using Vlookup (1st time user)

  • Thread starter Thread starter navneetjn
  • Start date Start date
N

navneetjn

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
:confused: Navneet :confused:
 
Maybe try put a $ sign in front of the first A2

ie.
=VLOOKUP($A2,Detail!$A$2:$F$29497,5,FALSE)
 
That didnt help!!
Thanks anyway. Does it have to do anything with the fact that I am
using Excel 97.
 
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)
 
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
 
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.
 
That did work. Amazing!!!
I just went to the worksheet "Detail" Column A and retyped the numbe
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 t
go and retype >1200 entries. Wow thats too much for a day!!! It jus
defeated my original purpose of saving time.
Any way thanks a lot, if you figure out why excel 97 does that do le
me know.
;) Navneet ;)
 
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.
 
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* :eek:

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
 
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.
 
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 said:
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 said:
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
:confused: Navneet :confused:


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25330
View this thread: http://www.excelforum.com/showthread.php?threadid=388108
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
:confused: Navneet :confused:


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25330
View this thread: http://www.excelforum.com/showthread.php?threadid=388108
Click to expand...
Click to expand...
Click to expand...
 
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
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" <[email protected]>
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 said:
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

:


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
:confused: Navneet :confused:


--
navneetjn

------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25330
View this thread:
http://www.excelforum.com/showthread.php?threadid=388108
Click to expand...
Click to expand...
Click to expand...
 
Appreciate the feed-back.
--

Regards,

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

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
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" <[email protected]>
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 said:
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

:


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
:confused: Navneet :confused:


--
navneetjn

------------------------------------------------------------------------
navneetjn's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25330
View this thread:
http://www.excelforum.com/showthread.php?threadid=388108
Click to expand...
Click to expand...
Click to expand...
 
Back
Top