VLOOKUP (2000)

Topic

New Reply

I have fooled with this thing for over a day and am very frustrated. On the attached workbook I have two sheets the first sheet called calendar starts in B2 with the formula Today() the next cell over has the formula b2+1 making it the next day and so forth accross the row. The other sheet is a table contaiing events. What I want to have happen is that under b2 or c2 or d2 lookup in the events table and list the events that match the date above it. Everytime I try to set up a lookup function and use the row 2 reference as a value it sums it and does not handle it as a date. Can anyone help me with this?

Reply | Quote

Replies

The dates and times in the Events worksheet all have an apostrophe ‘ before them (visible in the formula bar). This means that all these cells are interpreted as text, not as dates or times.

Reply | Quote

The dates and times in the Events worksheet all have an apostrophe ‘ before them (visible in the formula bar). This means that all these cells are interpreted as text, not as dates or times.

Reply | Quote

To followup on Hans’ message:
To convert your “text” dates and times to real dates and times:
put the number 1 into a cell. Copy it (ctrl-c)
select your columns A-D
edit paste-special multiply
Now all your “text” items are numbers. Format as desired

Bonus tip
If col A nd col C are date and times that go together (and B & D go together) you could combine them into one column by simple additon:
=A2+C2 (and/or =B2+D2)
to yield a date and time together (this can be formatted: “mmm d, yyyy hh:mm am/pm” to display something like: “Nov 18, 2003 7:34 AM”)

The “date” is the integer portion, the “time” is the decimal portion. Time (in excel) is stored as a fraction of a day.
Steve

Reply | Quote

Another way is to select each column in turn, Data Menu, Text to Columns, Finish.

Reply | Quote

Yes that is another good way.

But, your method only works on one column at a time. The method I proposed will work with the entire range in one operation.

Steve

Reply | Quote

I guess it depends on how many columns you have to deal with, I always use the way I described as it doesn’t involve writing and deleting a 1 to and from the sheet and it seems quicker, at least for a few columns. I seem to remember that someone, maybe Hans or Jan, wrote a macro that multiplied each cell in the selection by 1 to achieve the same result, but I don’t have a copy of it, and without the search function…..

Reply | Quote

I guess it depends on how many columns you have to deal with, I always use the way I described as it doesn’t involve writing and deleting a 1 to and from the sheet and it seems quicker, at least for a few columns. I seem to remember that someone, maybe Hans or Jan, wrote a macro that multiplied each cell in the selection by 1 to achieve the same result, but I don’t have a copy of it, and without the search function…..

Reply | Quote

Yes that is another good way.

But, your method only works on one column at a time. The method I proposed will work with the entire range in one operation.

Steve

Reply | Quote

Another way is to select each column in turn, Data Menu, Text to Columns, Finish.

Reply | Quote

To followup on Hans’ message:
To convert your “text” dates and times to real dates and times:
put the number 1 into a cell. Copy it (ctrl-c)
select your columns A-D
edit paste-special multiply
Now all your “text” items are numbers. Format as desired

Bonus tip
If col A nd col C are date and times that go together (and B & D go together) you could combine them into one column by simple additon:
=A2+C2 (and/or =B2+D2)
to yield a date and time together (this can be formatted: “mmm d, yyyy hh:mm am/pm” to display something like: “Nov 18, 2003 7:34 AM”)

The “date” is the integer portion, the “time” is the decimal portion. Time (in excel) is stored as a fraction of a day.
Steve

Reply | Quote

Assuming I resolve that problem. What code do I use in the cell below the dates in Calendar that will list what ever matches that date on the events page. Just the subject not every column.

Reply | Quote

If your dates are not sorted (like your example) then:

=VLOOKUP(B2,Events!$A$2:$E$67,5,FALSE)

will give you the value. It must be an EXACT match to the date in col A (and if that is the case, do not add the time as I suggested earlier)

If you do not want an exact match, but the “closest without being later” the dates in A must be sorted and you would use:

=VLOOKUP(B2,Events!$A$2:$E$67,5)

Steve

Reply | Quote

If your dates are not sorted (like your example) then:

=VLOOKUP(B2,Events!$A$2:$E$67,5,FALSE)

will give you the value. It must be an EXACT match to the date in col A (and if that is the case, do not add the time as I suggested earlier)

If you do not want an exact match, but the “closest without being later” the dates in A must be sorted and you would use:

=VLOOKUP(B2,Events!$A$2:$E$67,5)

Steve

Reply | Quote

Assuming I resolve that problem. What code do I use in the cell below the dates in Calendar that will list what ever matches that date on the events page. Just the subject not every column.

Reply | Quote