+ Reply to Thread
Results 1 to 8 of 8

Index Match with multiple criteria, but output value on different row?

  1. #1
    Forum Contributor
    Join Date
    08-25-2015
    Location
    London
    MS-Off Ver
    MS 365
    Posts
    213

    Question Index Match with multiple criteria, but output value on different row?

    Hi all,

    Happy Friday! Another good day to solve a puzzle haha...

    I have a super complex spreadsheet that I sadly cannot change in terms of format (so please don't suggest that ).
    • In range A4:A13, I have repeating values (e.g. value1, value2, value1, value3, value1 etc.)
    • In range B3:G2 I have the output value I want to essentially get to
    • the range B4:G13 contains 3 different values (X, Y, Z)
    • I want to create a (vertical) list for each of the values in range A4:A13
    • Each list should show the output in range B3:G2, based on if the index match cell contains X, Y or Z
    • The output values should be unique, e.g. if there are several X, Y or Z against value3, it should only list the output once

    I've attached the example file. Hope this all makes sense? Makes my head explode!

    Appreciate any help!
    Attached Files Attached Files
    Last edited by AliGW; 06-24-2022 at 01:25 PM. Reason: Irrelevant section of title removed.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Index Match with multiple criteria, but output value on different row?

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Index Match with multiple criteria, but output value on different row?

    with Power Query to change the data.

    After that a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    08-25-2015
    Location
    London
    MS-Off Ver
    MS 365
    Posts
    213

    Re: Index Match with multiple criteria, but output value on different row?

    Hey Fluff13,

    Thanks for the super speedy response! If I use that formula in A18 it correctly outputs "Output1", however, if I then drag down, the value stays "Output1", when the value in A19 should be "Output4" and in A20 "Output5".
    So this doesn't seem a formula I can drag? Sorry, I should said that the master file has hundreds of rows and columns, not just the few in the example file...

    Hope you can help?
    Cheers!

  5. #5
    Forum Contributor
    Join Date
    08-25-2015
    Location
    London
    MS-Off Ver
    MS 365
    Posts
    213

    Re: Index Match with multiple criteria, but output value on different row?

    Hey oeldere,

    Thanks for the suggestion! Looks complex (not very familiar was PowerQuery) and not exactly giving me the list that I need (sorry).
    Appreciate the help though.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Index Match with multiple criteria, but output value on different row?

    Typo, it should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-25-2015
    Location
    London
    MS-Off Ver
    MS 365
    Posts
    213

    Re: Index Match with multiple criteria, but output value on different row?

    OMG! I can't believe it - this actually works! WOHOO! Thank you so much - made my day!

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Index Match with multiple criteria, but output value on different row?

    You're welcome & thanks for the 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. Index Match : To retrieve output based on two criteria.
    By manish2303 in forum Excel General
    Replies: 1
    Last Post: 09-02-2019, 01:59 PM
  2. [SOLVED] complex if with index & match formula based on criteria in a column to extract data
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2018, 01:06 PM
  3. [SOLVED] Complex LOOKUP and Return Criteria possibly using INDEX/MATCH...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 04-19-2017, 08:43 AM
  4. Combine Multiple Complex Index Match Formulas to one Formula
    By Brawnystaff in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2015, 10:55 PM
  5. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  6. Index match formula for multiple criteria
    By nellyc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-03-2014, 12:03 PM
  7. Excel 2007 : Index Match Multiple Criteria Formula
    By paddyboy in forum Excel General
    Replies: 17
    Last Post: 03-17-2012, 12:01 AM

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