+ Reply to Thread
Results 1 to 5 of 5

Nested Match(index()) vs. Match() array formula.

  1. #1
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Unhappy Nested Match(index()) vs. Match() array formula.

    Hello,

    I'm trying to get over my hatred of array formulas, so I've been looking through the forums for awesome array-formula examples...

    This one has me stumped and I was hoping to get some help....on my "lowestPrice" sheet I have an array and non-array solution in columns D and F respectively.

    Basically, I'm trying to figure out why the array formula:
    Please Login or Register  to view this content.
    Is giving the same result as:
    Please Login or Register  to view this content.
    The array version makes sense to me....but the non-array version seems dimensionally incorrect or somethings...how is match bringing back the row with a static "0,1,1" for the index....does this mean that INDEX is bringing back the 0th row - 1st column value of an array?

    Can anyone explain what the non-array version with the Match(Index(),0,1,1) nesting means exactly?
    Attached Files Attached Files
    Last edited by GeneralDisarray; 10-14-2011 at 07:58 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested Match(index()) vs. Match() array formula.

    Here is a good read about the "magic" of the INDEX function, that may help you better understand it.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Nested Match(index()) vs. Match() array formula.

    was hoping you were on...as you were the one that posted that soln' to begin with

    thank you. will definitely read through it...
    Last edited by GeneralDisarray; 10-13-2011 at 03:15 PM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested Match(index()) vs. Match() array formula.

    So by replacing

    (A3=$I$3:$I$11)*(E3=$J$3:$J$11)

    with

    INDEX((A3=$I$3:$I$11)*(E3=$J$3:$J$11),0)

    you don't need to confirm with CSE as the INDEX with the 0 row, coerces the return of an array of values. In this case 1's and 0's since you are multiplying two conditional arrays together. Then the 1 is matched against the first 1 in the INDEX resultant array to return corresponding position.

    Use the Evaluate formula tool in Formulas tab to see the steps.

  5. #5
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Nested Match(index()) vs. Match() array formula.

    yep, i got the 'vector' of ones and zeros part, where: true*false or false*true or false*false = 0 and true*true=1

    thank you again for the link, the 0 in the "rows" place signals to return an entire array of values...never would have just figured that out on my own

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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