+ Reply to Thread
Results 1 to 5 of 5

INDEX MATCH 4 Columns return value from 5th column

  1. #1
    Registered User
    Join Date
    09-18-2008
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    66

    INDEX MATCH 4 Columns return value from 5th column

    Morning all,

    I have a large list of numbers sorted into 4 columns.

    I am trying to match the 1st 4 columns (dynamic list) with a 4 column static list of possible permutations.
    If the formula finds a match it should return a predetermined value for that particular combination of numbers.

    there is plenty of info on how to do this with 2 columns but I can't get my head around what is required to match 4 columns.

    The dynamic list is organised this way, For 50,000 rows (so far!)
    A B C D
    1ST 2ND 3RD 4TH
    2 3 6 1
    8 1 2 12
    1 11 5 6
    4 10 1 8
    1 4 6 7
    2 7 11 8
    7 1 2 3
    1 7 6 3
    2 11 4 1

    I have another range that covers all possible permutations of the 1st four for #'s 1 -12

    INDEX # #1 #2 #3 #4
    1 1 2 3 4
    2 1 2 3 5
    3 1 2 3 6
    4 1 2 3 7
    5 1 2 3 8
    6 1 2 3 9
    7 1 2 3 10
    8 1 2 3 11
    9 1 2 3 12

    etc,etc until...

    11875 12 11 10 4
    11876 12 11 10 5
    11877 12 11 10 6
    11878 12 11 10 7
    11879 12 11 10 8
    11880 12 11 10 9

    If a match is found for columns a,b,c,d I would like to be able to return the index #1-11880 for that row.

    Attached is a sample of what I am trying to achieve.

    Cheers
    Martin
    Attached Files Attached Files
    Last edited by Lungfish; 07-07-2018 at 05:54 PM. Reason: spelling mistakes

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: INDEX MATCH 4 Columns return value from 5th column

    MATCH is only meant to work with 1 column or 1 row. There are ways to work with multiples columns, but sometimes there are easier ways.

    As your INDEX column contains numeric values, SUMIFS will work, as long as each set of 4 values in B:E is unique. As it appears to contain unique permutaitions, this should not be an issue.

    =SUMIFS(INDEXFF!A:A,INDEXFF!B:B,A2,INDEXFF!C:C,B2,INDEXFF!D:D,C2,INDEXFF!E:E,D2)
    Last edited by jason.b75; 07-07-2018 at 05:50 PM.

  3. #3
    Registered User
    Join Date
    09-18-2008
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    66

    Re: INDEX MATCH 4 Columns return value from 5th column

    Sweet. Thanks for that.

    Works like a charm.

    Cheers
    Martin

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: INDEX MATCH 4 Columns return value from 5th column

    You're welcome!

    Thanks for the feedback.

    For future reference, if you had to use INDEX and MATCH for multiple columns with non- numeric index results, then you would need something like

    {=INDEX(INDEXFF!A:A,MATCH(2,1/((INDEXFF!B:B=A2)*(INDEXFF!C:C=B2)*(INDEXFF!D:D=C2)*(INDEXFF!E:E=D2))))}

    Which should be used with dynamic ranges, not full columns, due to the poor efficiency of the formula (in your sample file, this would probably take a couple of minutes to calculate, compared to a couple of seconds for the sumfs method).

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: INDEX MATCH 4 Columns return value from 5th column

    you can try PowerQuery

    Power Query for
    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. Match Three Columns to Return a Value in another Column
    By MotorcycleGuy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-19-2017, 07:50 PM
  2. [SOLVED] Need to match two columns and return a third column value from a table
    By HRGIRL16 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2017, 02:18 AM
  3. Replies: 9
    Last Post: 12-16-2013, 04:02 AM
  4. Replies: 20
    Last Post: 04-15-2013, 01:06 PM
  5. Replies: 3
    Last Post: 05-24-2012, 10:10 AM
  6. Match 2 columns in 2 sheets and return the 3rd column
    By swadson in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-16-2011, 04:36 PM
  7. Replies: 6
    Last Post: 03-01-2011, 06:23 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