Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Lookup vs Index-Match function

    I have a monthly calendar worksheet. I also have a "special dates" worksheet in the same workbook. The "special dates" worksheet has columns
    A= MonthNr, B = DayNr, [several misc], and E = ShortDesc The data starts in Row 7 and is expandable downward. The data is in order by MonthNr, and DayNr. There are only a few entries for each month and max of one per day.

    The Calendar worksheet is laid out like a standard calendar with Columns A-G being Mon-Fri. And with many rows for each week. For each day of the month I want, in one cell for that day, to look into the "special dates" worksheet and display the entry in column E, if one exists.

    I put the following formula in for a test (using Ctrl-Shft-Enter to create an array formula). The calendar is Jan (MonNr = 1), F3 is contains the DayNr in the first week's rows.
    {=INDEX('Special Dates'!$E7:$E100,MATCH(1,('Special Dates'!$A7:$A100=1)*('Special Dates'!$B7:$B100=F3),0))}

    The formula seemed to work fine, however when I copied the formula to a day in the next week's row all the supposedly Absolute Cell References to 'Special Dates' jumped by the number of rows and columns I was from the first test. What am I doing wrong. The formula also needs to be modified to handle when there is no matching row in the 'Special Dates' worksheet.

    Second question, is there a simpler (or better) way to accomplish this. I have to copy whatever formula is used to each day in the month.
    PGT

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Pete
    Quote Originally Posted by Pete Townsend View Post
    when I copied the formula to a day in the next week's row all the supposedly Absolute Cell References to 'Special Dates' jumped by the number of rows and columns I was from the first test. What am I doing wrong.
    You've made the column references absolute, but not the row references.
    For example, here:
    Code:
    'Special Dates'!$E7:$E100
    to make the row references absolute you need $ signs in front of the row numbers like this:
    Code:
    'Special Dates'!$E$7:$E$100

    Quote Originally Posted by Pete Townsend View Post
    The formula also needs to be modified to handle when there is no matching row in the 'Special Dates' worksheet.
    If you are using XL 2007 or later, then you can use the IFERROR() function. I think your formula will be along the lines of:
    Code:
    {=IFERROR(INDEX('Special Dates'!$E$7:$E$100,
        MATCH(1,('Special Dates'!$A$7:$A$100=C$2)*('Special Dates'!$B$7:$B$100=$F3),0)),"")}
    C$2 and $F3 I've guessed, because I'm not 100% sure where your days and years are lined up.

    If you are using an earlier version than that, then you'll have to use IF() which will make your formula considerably longer.
    Code:
    {=IF(ISNUMBER(MATCH(1, ('Special Dates'!$A$7:$A$100=C$2)*('Special Dates'!$B$7:$B$100=$B3),0)),
        INDEX('Special Dates'!$E$7:$E$100,MATCH(1,('Special Dates'!$A$7:$A$100=C$2)*('Special Dates'!$B$7:$B$100=$F3),0)),"")}
    Hope that helps...
    Last edited by Colin Legg; 07-24-10 at 19:57.

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Colin,

    Col & ROW Absolute. I KNOW THAT. Why didn't I see it. Yuch! My face is red with embarassment.

    The calendar is a bit of personal stubbornness. I know there are several free calendar programs I could use, but, as much for learning as for anything else, I did this one myself to fit my personal preferences and to fit my DayPlanner.

    Each day is basically several rows. For example week one is rows 3-11, week 2 is rows 12-20, etc. The first row (3) has the date number, the second row (4) has Major holiday names, rows 5-9 are blank, 2nd last row (10) will contain the results of this lookup if any exists, and the last row (11) has the day-of-the-year number. So January 1 of 2010 is a Friday (Col F); F3 shows a "1" F4 shows "New Years Day", F10 is where this formula will go, and F11 shows "1" the first day of the year.

    I'm using Excel 2003 so I'll need to encapsulate the formula with
    IF(ISERROR,..,..) to account for nothing found, and then encapsulate that with IF(F3>0,..,..) cover blank boxes at beginning & end of Month. I hope that doesn't exceed the limit on variables in a formula. (I think it might)

    I'll upload the file for hysterical reasons and to permit plagiarism by anyone that's wierd enough to be interested.
    Attached Files Attached Files
    PGT

  4. #4
    Join Date
    Aug 2006
    Posts
    126
    It turned out that only the IF(ISERROR(Match...,"",INDEX...) was necessary as it would also catch the blank boxes. The final formula was:

    =IF(ISERROR(MATCH(1,('Special Dates'!$A$7:$A$100=3)*('Special Dates'!$B$7:$B$100=A147),0)),"",INDEX('Special Dates'!$E$7:$E$100,MATCH(1,('Special Dates'!$A$7:$A$100=3)*('Special Dates'!$B$7:$B$100=A135),0))) [note this is for month 3 and the A135 is a particular day number.

    Thanks to everyone.
    Pete
    PGT

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •