+ Reply to Thread
Results 1 to 2 of 2

Returning multiple columns with INDEX/MATCH?

  1. #1
    Registered User
    Join Date
    03-16-2016
    Location
    NYC
    MS-Off Ver
    2011
    Posts
    1

    Question Returning multiple columns with INDEX/MATCH?

    I have a bunch of data which I tagged in category columns, and I want to make a sheet that pulls a range of data if it's tagged in a certain category column. In my case, columns A - E have the data I want, and columns F - T are the category columns.

    Screen Shot 2016-03-16 at 11.14.22 AM.png

    So for category F, I want to make a sheet that pulls the data from columns A - E that have anything (a checkmark) in column F. I assume INDEX/MATCH is the way to go for this, but so far I can't get it to work. I used =INDEX(Q1!A2:F900,MATCH("✔",Q1!F2:F900,0)) which at first seemed to work, but then I noticed for some reason it was pulling duplicates (even though there are no duplicate rows in the target range). Any idea why that would be or if there's a better way to accomplish this?

    Sorry if this has been asked a million times, this has already eaten up so much of my time so I'm trying to find an answer as quickly as possible. If this has been covered in another thread, I'd be super grateful if you could link me to it!

    Thank you so, so much in advance!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,257

    Re: Returning multiple columns with INDEX/MATCH?

    On sheet Q1, in cell J2, insert the formula

    =IF(COUNTBLANK(F2:I2)<>4,"Extract","")

    and copy down to match your list.

    Then in the other sheet, in cell A2, array enter the formula (enter using Strl-Shift-Enter)

    =IF(COUNTIF('Q1'!$J:$J,"Extract")>=ROWS($A$1:$A1),INDEX('Q1'!$A:$E,LARGE((('Q1'!$J$1:$J$100)="Extract")*ROW('Q1'!$J$1:$J$100),COUNTIF('Q1'!$J:$J,"Extract")-(ROWS($A$1:$A1)-1)),COLUMN(A1)),"")

    then copy A2 across the B2:E2, and copy A2:E2 down until it returns blanks.
    Bernie Deitrick
    Excel MVP 2000-2010

+ 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. Returning multiple distinct/repeating values for vlookup or index-match
    By amatvien in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-21-2018, 01:14 PM
  2. Index/Match or VLookup returning Multiple Results
    By Stoppeh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2014, 08:31 PM
  3. [SOLVED] Two criteria Index Match on multiple sheets returning multiple values
    By Joak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2014, 10:03 AM
  4. [SOLVED] VLOOKUP (or Index/Match) across four columns -- and returning multiple matches
    By rcasey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-11-2013, 05:20 PM
  5. [SOLVED] Index match with multiple criteria and returning last value
    By L.Steele in forum Excel General
    Replies: 2
    Last Post: 05-30-2012, 02:27 PM
  6. Returning multiple entries with INDEX and MATCH
    By jessea in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-04-2011, 02:50 PM
  7. Returning MULTIPLE values with Index and Match
    By Fly in forum Excel General
    Replies: 1
    Last Post: 06-01-2006, 12:55 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