+ Reply to Thread
Results 1 to 8 of 8

Index match with 2 column criteria and dynamic row

  1. #1
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Index match with 2 column criteria and dynamic row

    Hi all,

    I trust you are all well!

    Please could you help with the attached, I have given it a go and my formula attempt is in R12. The worksheet attached is easy enough to follow, the 3 lots of criteria are in P9, P10 and P11.

    It is GDPR compliant and the representative data is in P12.

    Thank you all in advance for looking.

    Regards

    Mark
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Index match with 2 column criteria and dynamic row

    I guess the sheet is self explanatory to you, but I have no idea what you are trying to do
    Please explain in words what you are trying to achieve

  3. #3
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Index match with 2 column criteria and dynamic row

    Hi,

    It is an index match with multiple criteria please using a NON array formula using the 3 criteria in P9, P10, P11. Hope that helps.
    Mark

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Index match with 2 column criteria and dynamic row

    Sorry, I don't understand. Explain in words please

  5. #5
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Index match with 2 column criteria and dynamic row

    Hi Pepe,
    I am sorry for not explaining this in more detail and not sure how I can, hence the simple realistic & representative sample data. What I would like the answer from the criteria in P9, P10 and P11 using an Index Match formula.
    e.g. date 21/09/2019 (column b, row 12), time 02:00 (column c, row 12) and Volume (column F, row 12) would return the answer 12. The issue I have is that columns may expand and today column F (Volume (Absolute Act)) may be column g tomorrow.
    Does that help?

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

    Re: Index match with 2 column criteria and dynamic row

    How about
    =IFERROR(INDEX(B4:N4997,AGGREGATE(15,6,(ROW(B4:B4997)-ROW(B4)+1)/(B4:B4997=P9)/(C4:C4997=P10),1),MATCH(P11,B3:N3,0)),"")

  7. #7
    Forum Contributor
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    104

    Re: Index match with 2 column criteria and dynamic row

    Cracking Fluff, thank you

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

    Re: Index match with 2 column criteria and dynamic 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. [SOLVED] Index/match dynamic result column
    By babymax in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-15-2020, 07:14 AM
  2. Index, Match, Dynamic column doesn't work
    By chuckf201 in forum Excel General
    Replies: 9
    Last Post: 11-17-2019, 08:08 PM
  3. [SOLVED] Index Match with a dynamic range (using 3 criteria types)
    By MarkoGreenio in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-02-2019, 01:55 PM
  4. INDEX/MATCH with multiple criteria and dynamic worksheet reference
    By dutchcourage in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-26-2019, 06:41 PM
  5. INDEX(MATCH) dynamic column range?
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2016, 03:46 PM
  6. Dynamic Index/Match function when column location is unknown?
    By ac1987 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-10-2014, 09:47 PM
  7. Replies: 6
    Last Post: 11-08-2013, 10:29 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