Forum Discussion
Solved
Lookup across spreadsheet- Random Locations for Headings
Hoping there is a way to lookup the Firstname, Surname, Data1, Data2 and populate the list as shown below- This is a spit out from a database and as such the data can be randomly plotted on the outpu...
Another approach. It's set to handle data like in the example but could be adjusted as needed.
'ReShape =LAMBDA(matrix,LET( header, {"First Name", "Last Name", "Data1", "Data2", "Data3", "Data4"}, BinVector, TOCOL( N((matrix <> "") * (LEFT(matrix, 4) <> "Data") * (RIGHT(matrix, 4) <> "Name")) ), seq, SEQUENCE(ROWS(BinVector)), r, WRAPROWS(FILTER(seq, BinVector <> 0), 6), reshaped, INDEX(TOCOL(matrix), r), VSTACK(header, reshaped) ))
An alternative could be these formulas along with a slightly different layout of the result table.
All formulas must be entered with ctrl+shift+enter if one doesn't work with Office365 or Excel for the web or Excel 2021.
=MATCH(F2&G2,$A$1:$A$28&$B$1:$B$28,0)This formula is in cell E2 and filled down to cell E5. E6 is filled manually with 100. 100 is a random number greater than the last row of the sample data (28).
=IFERROR(INDEX(A$1:A$28,SMALL(IF(A$1:A$28=F$1,ROW($A$1:$A$28)+1),ROW(A1))),"")This formula is in cell F2 and filled across range F2:G5.
=IFERROR(INDEX(IF(MOD(COLUMN()+3,3)=2,$B$1:$B$28,IF(MOD(COLUMN()+3,3)=0,$C$1:$C$28,$D$1:$D$28)),SMALL(IF((IF(MOD(COLUMN()+3,3)=2,$B$1:$B$28,IF(MOD(COLUMN()+3,3)=0,$C$1:$C$28,$D$1:$D$28))=H$1)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($A$1:$A$28)+1),1)),"")This formula is in cell H2 and filled across range H2:S5.