+ Reply to Thread
Results 1 to 6 of 6

Index Match across multiple Sheets to return results

  1. #1
    Registered User
    Join Date
    07-05-2019
    Location
    Wigan,England
    MS-Off Ver
    2013
    Posts
    3

    Index Match across multiple Sheets to return results

    under sheet 'count lookup' i have a results table displaying values from the same row based on a given value. this however is only looking at one sheet. how can i change the value to look at all the sheets.

    -i have 5 different building sites
    -each site has the number of how many bricks have been laid in each house
    i would like to be able to search across all sites by typing in the site and plot number to return the amount of bricks laid

    Thanks

    sorry protection code '6504'
    Last edited by awallace24335; 07-05-2019 at 03:57 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Match across multiple Sheets to return results

    1. make a list of sites and assign a named range (sites). Then either:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sites&"'!A7:A100"),$A3,INDIRECT("'"&Sites&"'!M7:M100")))

    drag across and manually adjust the columns in red, or, a more complicated formula that is draggable;

    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sites&"'!A7:A100"),$A3,INDIRECT("'"&Sites&"'!"&SUBSTITUTE(ADDRESS(1,COLUMNS($A:M),4),1,"")&"7:"&SUBSTITUTE(ADDRESS(1,COLUMNS($A:M),4),1,"")&"100")))

    2. And for the "yes" count...

    =SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sites&"'!A7:A100"),$A3,INDIRECT("'"&Sites&"'!Q7:Q100"),"Yes"))
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    07-05-2019
    Location
    Wigan,England
    MS-Off Ver
    2013
    Posts
    3

    Re: Index Match across multiple Sheets to return results

    Thanks Glen
    i have tried this, However its coming out with #Ref. i don't think its asking for the site name as the criteria from cell B2. i have created a range called sites

    Thanks for your help

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Match across multiple Sheets to return results

    Well... do you agree that it was working OK on the file I posted? If so, it must be something that you have done incorrectly. can you post your file with the non-working formula?

  5. #5
    Registered User
    Join Date
    07-05-2019
    Location
    Wigan,England
    MS-Off Ver
    2013
    Posts
    3

    Re: Index Match across multiple Sheets to return results

    i have got it to work but its not counting from the single sheet
    what is happening is, if there is two plot 44's on separate sites it will add the amount of bricks for the both sites rather then the site selected

    Thanks

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Match across multiple Sheets to return results

    Post a sample sheet.

+ 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. Replies: 1
    Last Post: 06-27-2017, 07:54 PM
  2. [SOLVED] Sum Index/Match results across multiple sheets
    By wiggy_worm in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-12-2016, 01:40 AM
  3. Index/Match Formula to return multiple results
    By MikeSta4ord in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-11-2015, 11:32 AM
  4. [SOLVED] SUM(IF(INDEX(MATCH()))) Confusion to return multiple results and count how many
    By athickett in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-06-2014, 02:46 PM
  5. Using Index And Match To Return Potential Multiple Results To One Cell
    By jcaynes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-11-2013, 10:41 AM
  6. Using Index Match to return multiple results with very messy data.
    By falkon007 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-30-2013, 12:28 PM
  7. Can Index or Match return multiple results?
    By waverider in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-30-2007, 10:06 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