matching excel columns

[Guest]

I am using EXCEL 2003. I have a worksheet with a column of all dates from
1/1/1900 through the present. I am getting new data in two columns. One of
dates, and one of information to match the dates. I need to add a column to
my original worksheet showing the information I need for specific dates. How
can I get excel to automatically find the matching date, and put only the
information in the second column in a new column next to the corresponding
date?

 

[Guest]

If I understand correctly, you have something like this:

Column A Column B
2/1/2007 Text
2/2/2007 Text
2/3/2007 Text

And in another worksheet (same workbook maybe?)

Column A
2/1/2007
3/1/2007

and you want to locate column B from worksheet 1 above and enter it into,
say, column B of worksheet 2.

If so,

in column B of sheet 2
=vlookup(A1,Sheet1!A1:B200,2,FALSE)

will return the matching text from the date on sheet1. If the date doesn't
exist in column 1, you'll get an #N/A in the field. To avoid this:

=if(iserror(vlookup(A1,Sheet1!A1:B200,2,FALSE),"",vlookup(A1,Sheet1!A1:B200,2,FALSE))

will leave a blank instead.

 

[Guest]

Thank you Sean. I tried it, but I got #N/A for every field. The information
that should have come up in Column B of worksheet 2 did not come up. I
appreciate your help.

 

[Guest]

Have to ask... are you sure there are dates that match on each sheet? Oh, and
most importantly, do the dates also have times listed? (click on the cell and
look in the formula bar to be sure)

It may be best to, in another column, type =round(A2,0) and paste down to
get rid of the hours and minutes.
Then do a VLOOKUP as below looing at the rounded values...

 

[Guest]

Actually, my problem is slightly different from the way you described it.
Worksheet one has SOME dates in Column A, with data in Column B.
Worksheet two has ALL Dates from 1900 to the present. I must put in Column
B of Worksheet two the information that is found for the matching date in
worksheet 1 in column B. Please help. Thank you. It would probably be easier
for me if I can have worksheet One with all the dates, and worksheet two
with some of the dates that have to be matched to worksheet one.
newyorkjoy
thanks for the help!

 

[Pete_UK]

You need to make the row references in Sean's formula into absolute,
and of course adjust them to the number of rows you have in your data
in Sheet1, i.e.:

=if(iserror(vlookup(A1,Sheet1!A$1:B$500,2,0),"",vlookup(A1,Sheet1!A$1:B
$500­,2,0))

I've used 0 instead of FALSE - does the same thing - and assumed you
have data up to row 500.

Hope this helps.

Pete

 

[Guest]

Pete and Sean. Please help. I revised my question, but I think everyone read
it before I revised it. Please read my third post where it explains the
problem better. Thank you!

 

[Pete_UK]

If the worksheet with your dates and data has a different name than
Sheet1 (eg Sheet2) then just change that in the formula. If you have,
say, 250 rows of dates and data in Sheet2, then change the 500 (in my
formula) to 250. If the worksheet which has all the dates has the
first date in A2, then enter this formula in B2:

=if(iserror(vlookup(A1,Sheet2!A$1:B$250,2,0),"",
vlookup(A1,Sheet2!A$1:B$250­,2,0))

and copy the formula down for as many dates as you have in column A.
If your sheet name has a space in it, eg Sheet 2, then you will need
to put apostophes around it, like so:

=if(iserror(vlookup(A1,'Sheet 2'!A$1:B$250,2,0),"",
vlookup(A1,'Sheet 2'!A$1:B$250­,2,0))

and then copy this down. This is all one formula - I've split it
manually to avoid awkward line breaks.

If you still have problems, then perhaps you should post a few
examples of the rows of data you have, and tell us what your sheet
names are.

Hope this helps.

Pete