Find the corresponding row ID and column ID of top 3 values in a matrix in excel

Question

I have the following matrix.

The output I want to receive is.

I have implemented LARGE() function to find the top 3 values. I have tried implementing INDEX() and MATCH() functions in one formula to receive the above output but was unsuccessful. How can we get the above output in excel?

Answer 1

Try using the following formula:

---

• Formula used in cell H1

=LET(
     a,TOCOL(B2:F6),
     b,TOCOL(IFNA(A2:A6,SEQUENCE(,ROWS(B2:F6)))),
     c,TOCOL(IFNA(B1:F1,SEQUENCE(ROWS(B2:F6)))),
     d,TAKE(SORT(HSTACK(a,b,c),1,-1),3),
     VSTACK({"Rank","Value","RowID","ColID"},
     HSTACK(SEQUENCE(ROWS(d)),d)))

---

This is another alternative formula but the number of bytes is more as well it uses LAMBDA( ) helper function BYROW( )

=LET(
     a,A1:F6,
     b,TAKE(SORT(TOCOL(DROP(a,1,1)),,-1),3),
     c,BYROW(b,LAMBDA(m,CONCAT(REPT(DROP(a,1,-5)&"|"&TAKE(a,1,-5),m=DROP(a,1,1))))),
     d,HSTACK(SEQUENCE(ROWS(b)),b,TEXTBEFORE(c,"|"),TEXTAFTER(c,"|"))+0,
     VSTACK({"Rank","Value","RowID","ColID"},d))

---

Bit less bytes than the earlier one:

=LET(
     a,A2:A6,
     b,B1:F1,
     c,B2:F6,
     d,TAKE(SORT(TOCOL(c),,-1),3),
     e,SEQUENCE(ROWS(d)),
     HSTACK(VSTACK("Rank",e),REDUCE({"Value","RowID","ColID"},d,LAMBDA(x,y,
     VSTACK(x,HSTACK(y,CONCAT(REPT(a,y=c)),CONCAT(REPT(b,y=c)))+0)))))

Answer 2

Here is an alternative:

Formula in H1:

=VSTACK({"Rank","Value","RowID","ColID"},HSTACK({1;2;3},TAKE(SORT(--TEXTSPLIT(TEXTAFTER("|"&TOCOL(B2:F6&"|"&A2:A6&"|"&B1:F1),"|",{1,2,3}),"|"),,-1),3)))