+ Reply to Thread
Results 1 to 2 of 2

Index match multiple arrays

  1. #1
    Registered User
    Join Date
    03-28-2014
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    3

    Index match multiple arrays

    Hi,

    I am trying to lookup a column in different sheets and pull through a consolidated list of only unique values. I am using the below formula to do this on one sheet of data, but I have multiple sheets I need to consolidate.

    =IFERROR(INDEX(Sheet2!$B$5:$B$12,MATCH(0,INDEX(COUNTIF(B$3:$B4,(Sheet2!$B$5:$B$12)),0,0),0)),"")

    I've also attached an example worksheet.

    Any help would be greatly appreciated!

    Thanks,
    Luke
    Attached Files Attached Files

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Index match multiple arrays

    Hi

    Use two helper columns D and E.
    In D2:Dn make a list of the sheets you want consolidate.
    Set E1 equal to the first row for your list (in your example 5). In E2:En use and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Use in B5:Bn the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the file for clarification.
    Attached Files Attached Files

+ 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 function with multiple arrays
    By georgedixon in forum Excel General
    Replies: 6
    Last Post: 09-15-2017, 01:49 AM
  2. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  3. INDEX/MATCH with multiple arrays. Possibly need IF Function as well?
    By guy lafleur in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-31-2015, 03:13 PM
  4. INDEX & MATCH Function with Multiple Arrays
    By btone in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2014, 03:59 PM
  5. Problem with reference to multiple arrays in INDEX/MATCH combo
    By tnuis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-24-2013, 08:10 PM
  6. Problems with reference to multiple arrays using VLOOKUP / INDEX-MATCH
    By tnuis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2013, 07:21 AM
  7. [SOLVED] Index & Match returning incorrect value. Arrays fixed and exact match used.
    By SDes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 08:29 PM

Tags for this Thread

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