+ Reply to Thread
Results 1 to 4 of 4

Index Match with Horizontal and Vertical Matches

  1. #1
    Registered User
    Join Date
    10-21-2013
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Index Match with Horizontal and Vertical Matches

    I have a budget related spreadsheet that calculates rates based on various criteria.

    1. Either original budget or forecast budget
    2. Either rehearsal rate or concert rate
    3. Either regular pay rate or doubling pay rate
    4. Either of two contract term time periods

    I have the spreadsheet working except for the fact that I can not figure out how to have the entire formula determine if its one contract term vs. another. Currently, the example spreadsheet formula (built in the project tab) is only looking at the one row of contract term rates. But if you look in the project spreadsheet, you'll see that in column A, the row is based on one of two contract term time periods. The rate grid is also based on two contract term time periods but my formula isn't acknowledging both contract term periods at this point.

    I started this whole formula with a vlookup on the contract term period and the rehearsal rate or concert rate fields. That all worked perfect. Then I realized that that method would not capture the fact that there are two types of pay rates - regular or doubling.

    Is the rate grid even set up the right way? This can be redone if this would help resolve my issue.

    I realize at this point that I have two horizontal matches and I'm trying to add in a vertical match....is that possible?

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Index Match with Horizontal and Vertical Matches

    Though I do not understanding clearly, but for the second term, your reference data :
    'Rate Grid'!$C$8:$N$8
    may be:
    'Rate Grid'!$C$9:$N$9
    and the result wil be corrected.
    Quang PT

  3. #3
    Registered User
    Join Date
    10-21-2013
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Index Match with Horizontal and Vertical Matches

    I can not use your suggested formula above since it needs to run off of row 8 OR 9. The problem is that I need to add column A from the project tab into the formula. Right now, the formula is only set to look at Row 8 (as it was built) but what I need it to do it look at row 8 OR row 9 of the rate grid based on what 's in column A of the project tab. Does that help state my question better? Thank you!

  4. #4
    Registered User
    Join Date
    10-21-2013
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Index Match with Horizontal and Vertical Matches

    Index Match2.xlsxAlright, I figured out the answer to my own question. The formula I came up with is as such:

    =IF($B4="9/15-12/15",INDEX('Rate Grid'!$C$8:$N$8,MATCH(1,(Project!$D$1='Rate Grid'!$C$4:$N$4)*(Project!H$2='Rate Grid'!$C$5:$N$5),0))*Project!I4*Project!$D4+INDEX('Rate Grid'!$C$8:$N$8,MATCH(1,(Project!$E$1='Rate Grid'!$C$4:$N$4)*(Project!H$2='Rate Grid'!$C$5:$N$5),0))*Project!I4*Project!$E4,IF($B4="12/16-8/31",INDEX('Rate Grid'!$C$9:$N$9,MATCH(1,(Project!$D$1='Rate Grid'!$C$4:$N$4)*(Project!H$2='Rate Grid'!$C$5:$N$5),0))*Project!I4*Project!$D4+INDEX('Rate Grid'!$C$9:$N$9,MATCH(1,(Project!$E$1='Rate Grid'!$C$4:$N$4)*(Project!H$2='Rate Grid'!$C$5:$N$5),0))*Project!I4*Project!$E4,0))

    Now I'd like to know if there is an easier way to do this.

    I was hoping to avoid writing the formula so that 1) it was locking the formula into being based on a defined if statement....what if the dates need to change. Then I'll have to find / replace very worksheet in the entire workbook for this formula. 2) What if new contract terms - date range needed to be created. It requires me to extend the formula, vs. just updating a named range.

    I'm still hoping for someone to point out an easier way to set up the Rate Grid or the formula itself to look up vertically and horizontally off of numerous criteria.

    Thanks.
    Katie

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDEX MATCH array formula that matches substring n gives multiple matches
    By bkwins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 04:57 AM
  2. [SOLVED] Sum based on horizontal and vertical match
    By Prcntrygrl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-29-2013, 01:41 PM
  3. [SOLVED] Using Index & Match to search 2 vertical columns and return data to a vertical set
    By QuietLife in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2013, 09:59 AM
  4. Replies: 4
    Last Post: 07-18-2012, 02:34 PM
  5. simple excel help. make vertical data horizontal according to index
    By dancmin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2009, 04:31 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1