how to return the row number of all rows from a bridge table in excel

Question

I have a bridge table and a row of labels like below

How can I return the row number of each label (A, B, and C). The expected output is list below.

Thanks for your help

Answer 1

Assuming you need the row number not the index position within an array. This solution spills the entire result all at once. You can use the following formula in D1 cell:

=LET(B, B2:B9, ux, UNIQUE(B), rows, DROP(REDUCE("", ux, LAMBDA(ac,u, 
 HSTACK(ac, FILTER(ROW(B2:B9), B=u)))),,1), VSTACK(TOROW(ux), rows))

We use the DROP/REDUCE/HSTACK pattern to concatenate each column per iteration of REDUCE. Check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length for more details.

If you are curious why I don't use the name B in the ROW call, inside the LAMBDA function of REDUCE. The reason is that ROW(B) produces #VALUE!. Probably related to the same possible bug as it was documented in this question: Using name variable from LET produces #VALUE! inside MAP using SUM with the range defined as INDEX : INDEX, check @JosWoolley's answer. For example the following works, but it is not worth it to introduce an additional LET:

=LET(B, B2:B9, ux, UNIQUE(B), rows, DROP(REDUCE("", ux, LAMBDA(ac,u, LET(
  colB, B2:B9, HSTACK(ac, FILTER(ROW(colB), B=u))))),,1), VSTACK(TOROW(ux), rows)

or a more elegant solution is to create a LAMBDA function (B) that refers to the range B2:B9:

=LET(B, LAMBDA(B2:B9), ux, UNIQUE(B()), rows, DROP(REDUCE("", ux, LAMBDA(ac,u,
 HSTACK(ac, FILTER(ROW(B()), B()=u)))),,1), VSTACK(TOROW(ux), rows))

Check my answer to the question: MAXIFS doesn't work as expected invoked inside MAP using names from LET for more information. The above approach has a lower maintenance cost because you need to update the range just in one place.

Answer 2

You could use an array formula (entered with Ctrl + Shift + Enter). Later versions of Excel partic. 365 support more concise formulations than shown below where I use SMALL and IF. Essentially, create an array of 1|0 matches to the Category, use IF to filter out 0s, and use the row the formula is in to return the next smallest match.

Set-up

Formula in D2 then dragged across and down. Press Ctrl+Shift+Enter to ensure formula is an array formula:

=IFERROR(SMALL(IF(ROW($B$2:$B$9)*--($B$2:$B$9=D$1)>0,ROW($B$2:$B$9)*--($B$2:$B$9=D$1)),ROWS($C$2:$C2)), NA())

You could use same idea with MATCH

=IFERROR(SMALL(IF(ISNUMBER(MATCH($B$2:$B$9,D$1,0)),ROW($B$2:$B$9)*--($B$2:$B$9=D$1)),ROWS($C$2:$C2)), NA())

Answer 3

You can try with Textsplit & Textjoin functions

=--TEXTSPLIT(TEXTJOIN(",",,IF(D$1=$B1:$B9,SEQUENCE(ROWS(D1:D9)),"")),,",")

or try with Filter func but the formula seems lengthy..

=FILTER(IF(D$1=$B1:$B9,SEQUENCE(ROWS(D1:D9)),"#"),IF(D$1=$B1:$B9,SEQUENCE(ROWS(D1:D9)),"#")<>"#")