How to get the address of the formula's result from 2D array (Matrix)
Results 1 to 4 of 4

Thread: How to get the address of the formula's result from 2D array (Matrix)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to get the address of the formula's result from 2D array (Matrix)

    Hi everyone!

    I have stumbled upon a problem while trying to extract the address of the formula's result. Namely, I am talking about the formula that calculated minimum value from the matrix based on the different criteria.

    The matrix that I have created is simplified just for the purpose of this question.

    V. Criteria 1 V. Criteria 2 V. Criteria 3
    H. Criteria 1 22 33 44
    H. Criteria 2 22 33 55
    H. Criteria 3 25 44 11

    **
    If the "FORMULA" = 44, I would like to know the exact position of this result (the one bolded in the matrix)

    So far I have tried CELL("address" but id doesn't seem to work because my formula is MIN(IF array formula.

    I would appreciate any ideas or solutions.

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,552
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: How to get the address of the formula's result from 2D array (Matrix)

    You could do this:

    =ADDRESS(MATCH(TRUE,INDEX($A$2:$A$4="H. Criteria 3",0),0)+ROW($A$2)-1,MATCH(TRUE,INDEX($B$1:$D$1="V. Criteria 2",0),0)+COLUMN($B$1)-1,4)
    Looking for opportunities

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the address of the formula's result from 2D array (Matrix)

    Thank you for the answer Steve.

    My criteria is dynamic and it is changing with the user input. The result is calculated by the formula which is 1km long due to the complexity of the criteria.

    This wont work because I need something where I can input my formula and its array and ask for the coordinates of its result. Even though there are similar values to the result I got, I will need the one that my formula returned and not a duplicate value somewhere in the matrix.

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,552
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: How to get the address of the formula's result from 2D array (Matrix)

    The only way to do that i think is to search for the result in the matrix. That isnt as bulletproof as using the formula conditions to build the address formula.
    Looking for opportunities

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •