I start with a range of data (a formula for instance combining the column/row header and/or "text" and or empty items).
the first row above the range is a column header (number or letter or empty).
the first column left of data is a row header (descending numbers or letter or empty).
All data, column/row headers are unique no repetitions (except the blanks).
As output I would like to get 3 columns 1 | 2 | 3 data | col header | row header
this data being only non blank entries (example of formula if(TRUE/FALSE,B1&A3,""). I add this because it seems that the formula output cannot be referenced always ? if B1&A3 as data entry then it should take it in the list if "" then it should evaluate the cell as blank and omit from the list.
A particular solution, using TOCOL
s ignore parameter:
1 - Ignore blanks
2 - Ignore errors
=LET(
data, B2:E7,
col_header, A1:E1,
row_header, A1:A7,
result, VSTACK(
{"Label", "Col", "Row"},
HSTACK(
TOCOL(data, 1, TRUE),
TOCOL(IF(ISBLANK(data), NA(), INDEX(col_header, 1, COLUMN(data))), 2, TRUE),
TOCOL(IF(ISBLANK(data), NA(), INDEX(row_header, ROW(data), 1)), 2, TRUE)
)
),
result
)