+ Reply to Thread
Results 1 to 8 of 8

Combining Index & Match Functions

  1. #1
    Registered User
    Join Date
    07-28-2010
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Question Combining Index & Match Functions

    I have used the forum before and really received wonderful help, so I am hoping for much needed help again.

    I have a set of raw data on sheet1, which I reference to sheet2 to populate a particular form. The raw data specifies attributes for apartment communities, including rents by unit type. Ultimately, I am trying to populate sheet2 with information about 5 communities. Populating sheet2 for the basic attributes works fine with my standard Index & Match function for each of the 5 communities, but when I get to the point of populating sheet2 for unit specifics, it won't seem to work. Specifically, the first community works fine, but I can't figure out how to populate the next 4, it just pulls data for the first community? The reason its a little tricky, at least for me, is because each community may have several versions of the same basic unit type (3 different 1-bed/1-bath models, etc.), plus I can't seem to figure out how to properly flag the data I want to select in terms of populating the size, bed, bath, and rent fields. I have attached the basic file and would greatly appreciate any insight, it is beyond my capacity to figure out. I have highlighted the cell on sheet2 that is the problem area. I assume that once I resolve the formula for that cell, the others will be no problem. Thanks again.
    Attached Files Attached Files
    Last edited by bmc1975; 10-28-2010 at 11:06 AM.

  2. #2
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Combining Index & Match Functions

    I think since your file references external sheets, its a little hard to figure out what you are trying to achive since we cant see the other sheets!
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  3. #3
    Registered User
    Join Date
    07-28-2010
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Combining Index & Match Functions

    Sorry

    Attached is an updated file, w/o external links
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining Index & Match Functions

    Problem #1) Three MATCH functions inside the INDEX() function.

    You can only use 2 in my understanding.

    =INDEX(RangeToGetValues, MATCH(KeyVal1, VertRngToMatch, 0), MATCH(KeyVal2, HorizRngToMatch, 0))


    KeyVal1 is matched to the values down the left of your RangeToGetValues and the KeyVal2 is matched to the values across the top of your table.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining Index & Match Functions

    If you need to match by TWO values on the left side, you'll need to add an additional column to your data set to combine these two unique values into a single matchable field to make it easy on yourself.

    So if you have a string STUDIO and a string MARKET, and the value you want is on the row with the MARKET string, then on a new column on the MARKET row you enter the text string:

    Studio-Market

    Of course you continue that process down the new key column until you have values for each row.
    Now you match a concatenated string to that new column:

    MATCH(AG55 & "-" & AF16, Sheet1!$A$96:$A$258, 0)

  6. #6
    Registered User
    Join Date
    07-28-2010
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Combining Index & Match Functions

    thank you for your help, I will give it a try. It is much appreciated. If it works, I'll let you know and mark as solved. Thanks again.

  7. #7
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Combining Index & Match Functions

    One more option,

    =INDEX(Sheet1!$H$96:$AA$258,MATCH(1,INDEX((Sheet1!$F$96:$F$258=$AG$55)*(Sheet1!$B$96:$B$258=$AF$16),0),0),MATCH(AJ17,Sheet1!H1:AA1))

    Regards

  8. #8
    Registered User
    Join Date
    07-28-2010
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Combining Index & Match Functions

    Incredible, Thanks to all

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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