help with lookup

[DavidObeid]

I have a workbook made of 2 sheets.

Sheet1 has a list of dates down column B (starting at row 4). Thes
dates are in chronological order.

Sheet2 has a list of anniversaries down Column C (starting at C1) tha
are also in chronological order, with a text entry for each date in th
adjacent cell in column D.

I want my spreadsheet to automatically report into column D of sheet
the text entries from column D of sheet2 alongside the appropriat
date.

In other words,

Sheet1!$B$4 contains 1/Jan/2004

Sheet2!$C$1 contains 1/Jan/2004

Sheet2!$D$1 contains New Years Day

I want my spreadsheet to recognise that the date in sheet1 also occur
in sheet2 and then paste the comment "New Years Day" into Sheet1$D$4.

Not every date will have an anniversary celebrated on it, so I als
want the spreadsheet to avoid any error messages for blank cells.

Non-VBA solutions please.

Regards,

Dav

 

[Philip Reece-Heal]

David
Using your example references, the basic lookup formula in Sheet1$D$4 would
be:
=VLOOKUP(B4,Sheet2!C,2,FALSE)
If you want to avoid error messages for blank lookups, you could use the
following expanded formula instead:
=IF(ISERROR(VLOOKUP(B4,Sheet2!C,2,FALSE))=TRUE,"",VLOOKUP(B4,Sheet2!C,2,
FALSE))

All the best

Philip

 

[DavidObeid]

Phillip,

Thanks for the tip, but when I read it I get 2 smilieys in the formul
that I can't workout how to translate into characters.

What am I missing in place of the ampersands (&) in the following:

=IF(ISERROR(VLOOKUP(B4,Sheet2!C&,2,FALSE))=TRUE,"",VLOOKUP(B4,Sheet2!C&,2
FALSE))

Thanks in advance,

Dav

 

[Philip Reece-Heal]

David

Instead of ampersand (&) you should be typing D and colon ) i.e. the
character that is two dots, one above the other.
I can only imagine there is something different between the fonts you and I
are using.

One way to overcome problem would be to click on cell with the formula, and
in the formula bar highlight "Sheet2!C&,2" part of the formula and then
switch to Sheet 2 and click and highlight columns C & D and then press
Return. That should correct that part of the formula. Then do the same where
that part of the formula is repeated.

All the best
Philip

 

[DavidObeid]

Dear Phillip,

Thanks so much.

I think the "penny dropped" with using lookup functions after seenin
how your formula works on my spreadsheet.

Can I push the friendship and ask if you know how to determine (with
formula) which is the 2nd Sunday of a given month?

Thanks

Dav

 

[Philip Reece-Heal]

David
Glad you sorted your Lookup problem.

As regards 2nd Sunday in month, there may be more elegant solutions but the
following seems to work;

Using Excel's WEEKDAY function which numbers the day of the week for
particular dates:
If cell A1 contains the date 1/11/2003, and cell B1 contains the formula
=WEEKDAY(A1,2) it will give the answer 6. I.e., it's numbering days Monday 1
through to Sunday 7. (checkout WEEKDAY in Excel's Help to see the different
numbering systems that can be used).

Anyway, if you extend the formula in cell B1 to read
=IF(WEEKDAY(A1,2)<7,14-WEEKDAY(A1,2)+1,WEEKDAY(A1,2)+1) it will give the
answer 14 and 14/11/03 is the 2nd Sunday in November.

Remember this only works if the date in cell A1 is the 1st of the month

All the best
Philip