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