+ Reply to Thread
Results 1 to 4 of 4

Add a condition to an array function containing INDEX MATCH

  1. #1
    Registered User
    Join Date
    11-08-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    7

    Add a condition to an array function containing INDEX MATCH

    Hi,

    I'd like some help with an array function that i found online.
    I want the formula only to return the found match if cell in the L-column on the same row is equal to "Ombokas".

    This is the array formula:
    {=INDEX($C$3:$C$8, SMALL(IF(ISNUMBER(MATCH($B$3:$B$8, $E$3, 0)), MATCH(ROW($B$3:$B$8), ROW($B$3:$B$8)), ""), ROWS($A$1:A1)))}

    I have attached a file that contains the problem.
    In the context of the problem, I would only want the results 6, 4 and 1 to return if the cell in the L-column on each respective row where the data was gathered to be equal to "Ombokas". (the cell in LX should say "Ombokas")
    In the same example only the 6 should return.

    Thanks in advance!

    Erik W
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Add a condition to an array function containing INDEX MATCH

    Please try at E6
    =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($C$3:$C$8)/($B$3:$B$8=$E$3)/($L$3:$L$8=$L$3),ROWS(E$6:E6))),"")

  3. #3
    Registered User
    Join Date
    11-08-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    7

    Re: Add a condition to an array function containing INDEX MATCH

    Thank you very much for the reply,

    It did work, didnt think of using AGGREGATE but it was a clever and elegant solution.
    Many thnks to you,

    Erik W

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Add a condition to an array function containing INDEX MATCH

    Thanks, actually AGGREGATE(15, ) and SMALL(IF) are very similar, but no need Array enter for Aggregate


    =IFERROR(INDEX(C:C,SMALL(IF(($B$3:$B$8=$E$3)*($L$3:$L$8=$L$3),ROW($C$3:$C$8)),ROWS(E$6:E6))),"")

    This need Ctrl+Shift+Enter

+ 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] Dynamic array of an index match function
    By timmtamm in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-07-2019, 06:59 PM
  2. [SOLVED] Match Index Function for Large Array
    By takeawalkk in forum Excel General
    Replies: 10
    Last Post: 08-24-2017, 02:55 PM
  3. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  4. [SOLVED] INDEX/MATCH Array within LARGE Function?
    By DZ217 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-12-2015, 11:18 AM
  5. [SOLVED] Variable Row Array in Index Match Function
    By Lacaycer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2013, 11:45 AM
  6. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  7. Index/Match function inside an array
    By tittiot in forum Excel General
    Replies: 2
    Last Post: 01-20-2010, 09:48 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