+ Reply to Thread
Results 1 to 12 of 12

Can you match across multiple columns/rows?

  1. #1
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Can you match across multiple columns/rows?

    For example, say my sheet is like this:
    Please Login or Register  to view this content.
    with the 4th column containing desired outputs.

    If I wanted to do a match where it first checked if Column A was the same, then looked at if there were any trues in column B, and returned the value in column D whenever a True was found in column C, would it be possible?

    It would look like this:
    Please Login or Register  to view this content.
    I believe I'd have to use an array formula, but would prefer if there was a solution not requiring one, as the worksheet constantly changes.

    I've gotten the formula to almost work, but I am having a hard time coming up with a logical statement to divide column A into "sections." Any help in that area would be greatly appreciated. I'm basically trying to use a combination of if statements and index(match(), since those are about all I am familiar with. Maybe someone knows of a more elegant solution. So far, the closest thing I've come up with is utilizing the exact function to divide the A Column, but that only works for the first division, and returns all false for everything else.

    Sorry for the bad formatting, tried to illustrate my question as best as possible...

    Thanks in advance for any help.
    Last edited by TheN; 06-29-2016 at 06:23 AM. Reason: Better explanation

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Can you match across multiple columns/rows?

    Hi TheN and welcome to the forum,

    I'd use a helper column next to your data first. Then change what is in the yellow cells on my attached sheet. Then it is an easy Index Match problem.

    If this isn't what you want then you might consider using an Advanced Filter.
    Index Match with Concatenated Helper.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Can you match across multiple columns/rows?

    Try this in E2:

    =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,TRUE)*COUNTIFS($A$2:$A2,A2,$C$2:$C2,TRUE)*(C2=TRUE),D2,"")

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can you match across multiple columns/rows?

    Or this in E1.

    I don't see how J is not included. Could you explain that to me?

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Fruit
    TRUE
    FALSE
    A In E1: =IF(IF(IF(A1=$A$1:$A$10,COUNTIF($B$1:$B$10,TRUE)),$C$1:$C$10),D1,"")
    2
    Fruit
    FALSE
    TRUE
    B B
    3
    Fruit
    FALSE
    FALSE
    C
    4
    Fruit
    TRUE
    FALSE
    D
    5
    Veggie
    TRUE
    FALSE
    E
    6
    Veggie
    FALSE
    FALSE
    F
    7
    Veggie
    FALSE
    TRUE
    G G
    8
    Veggie
    TRUE
    FALSE
    H
    9
    Meat
    FALSE
    FALSE
    I
    10
    Meat
    FALSE
    TRUE
    J J
    Dave

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can you match across multiple columns/rows?

    Ahhh. I see now.

    Try array entering this in E1.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Can you match across multiple columns/rows?

    a little shorter ...

    =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,TRUE)*(C2=TRUE),D2,"")

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can you match across multiple columns/rows?

    A change in my previous formula .. shorter but still array entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can you match across multiple columns/rows?

    @ Phuocam

    Nice one.

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Can you match across multiple columns/rows?

    @ FlameRetired
    Thank you!
    Last edited by Phuocam; 06-29-2016 at 07:14 AM.

  10. #10
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Can you match across multiple columns/rows?

    Wow, thank you so much guys.

    I was really struggling with this, but you made it look so easy.

    I'll use Phuocam's answer as it is shortest and will be easiest to change if some conditions change, never thought about solving it like that, a very elegant solution indeed.

    Much appreciated to everyone that tried to help though.

  11. #11
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Can you match across multiple columns/rows?

    You are welcome!

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can you match across multiple columns/rows?

    You are welcome. And thank you for the kind words and feedback.

+ 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 (multiple critera in multiple rows and columns)
    By swma in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-01-2016, 10:02 AM
  2. INDEX MATCH across multiple rows and columns
    By kamran in forum Excel General
    Replies: 1
    Last Post: 06-19-2015, 01:42 PM
  3. [SOLVED] Index & Match Multiple Rows & Columns
    By brad_x81 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-22-2014, 07:26 AM
  4. How to delete multiple rows but that match in certain columns please help
    By xlettiix in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2014, 12:42 AM
  5. [SOLVED] Find exact match in multiple columns in 1 row with all other rows 90,000 rows.
    By KHurt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2013, 01:38 PM
  6. Index and Match with multiple columns/rows
    By fab121 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-18-2012, 02:33 PM
  7. Match multiple criteria in rows and columns
    By JuJuBe in forum Excel General
    Replies: 5
    Last Post: 06-02-2010, 03:47 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