Search code examples
excelmatrixindexingexcel-formulamatch

Extract values stored in diagonal elements with the rowid as an input in Excel

Let's say we have the following matrix, and we want to populate C2 by extracting the values stored in diagonal cells for the ROW Labels and COLUMN Headers matching the value in C1.

Matrix:

1 2 3 4 5
1 2416 6136 13519 5298 1333
2 459 2416 5225 12043 3292
3 785 1276 1319 3188 14798
4 605 816 689 1539 4529
5 205 221 152 301 459

Desired result

C1  C2
2   2416
5   459

I have tried using MATCH() and INDEX() function but could not get the desired result.

Note: In case the matrix is stored in another sheet in a table format and I want to use INDIRECT() function to read the values.

Solution

  • Try using INDEX() & MATCH()

    enter image description here

    • Formula used in cell I2

    =INDEX($B$2:$F$6,MATCH(H2,$A$2:$A$6,0),MATCH(H2,$B$1:$F$1,0))

    Or, SUM()

    enter image description here

    • Formula used in cell I2

    =SUM((H2=$B$1:$F$1)*(H2=$A$2:$A$6)*$B$2:$F$6)

    Or, using FILTER()

    enter image description here

    • Formula used in cell I2

    =FILTER(FILTER($B$2:$F$6,H2=$A$2:$A$6),H2=$B$1:$F$1)

    Or, using MAP()

    enter image description here

    • Formula used in cell I2

    =MAP(H2:H3,LAMBDA(x,FILTER(FILTER(B2:F6,x=A2:A6),x=B1:F1)))

    Or,

    • Formula used in cell I2

    =XLOOKUP(H2,$B$1:$F$1,FILTER($B$2:$F$6,H2=$A$2:$A$6))

    enter image description here

    Assumptions based on OP's comments:

    This formula: =XLOOKUP(H2,$B$1:$F$1,FILTER($B$2:$F$6,H2=$A$2:$A$6)) and the other formulas don't seem to work if the matrix is stored in another sheet and we are using indirect function to read it.

    Since I am not sure for what reason you are using the INDIRECT() function, as it is not clear and OP doesnt seems properly explained, I tried few ways which I have commented below, just assuming if you are using INDIRECT() to reference the SheetTab, then you could try.

    enter image description here

    • Formula used in cell B2

    =LET(
     α, INDIRECT(C1&"!A1:F6"),
     φ, DROP(TAKE(α,,1),1),
     δ, DROP(TAKE(α,1),,1),
     Ω, DROP(α,1,1),
     MAP(A2:A3,LAMBDA(m,FILTER(FILTER(Ω,m=φ),m=δ))))

    Also what I see clearly INDIRECT() is not required, but yes if you have more than one sheets and want to toggle sheets to different output, then its required however in modern excel there are few functions when on clubbing those you can bring down an array of data combined and then do your manipulations with the data. But still its not clear.

    One way:

    enter image description here

    Another way suggested by @P.b Sir,

    enter image description here

    If data in other sheet is in Structured References then

    enter image description here

    Final Edit & Update:

    enter image description here

    • Sheet1 --> is the place where data is stored.
    • Structured References --> Data is in Structured References aka Tables
    • OP, wants to specifically use INDIRECT() function.

    enter image description here

    • Formula used in cell B2

    =LET(
     α, INDIRECT(C1),
     φ, DROP(TAKE(α,,1),1),
     δ, DROP(TAKE(α,1),,1),
     Ω, DROP(α,1,1),
     MAP(A2:A3,LAMBDA(m,FILTER(FILTER(Ω,m=φ),m=δ/1))))

    Or,

    • in C2

    =LET(
     α, INDIRECT(C1),
     φ, DROP(α,1,1),
     δ, TOROW(IF(φ,DROP(TAKE(α,1),,1)))/1,
     Ω, TOROW(IF(φ,DROP(TAKE(α,,1),1))),
     MMULT((δ=A2:A3)*(Ω=A2:A3),TOCOL(φ)))

    Notes: And since the headers are numbers while in Structured References it converts to Text format therefore it was divided by 1 to make it numerical --> δ/1

    All solutions posted above can be found here: Excel

    enter image description here