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) ))
=IFERROR(IFERROR(INDEX($B$1:$B$28,SMALL(IF(($B$1:$B$28=H$1)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($B$1:$B$28)+1),1)),INDEX($C$1:$C$28,SMALL(IF(($C$1:$C$28=P$8)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($B$1:$B$28)+1),1))),INDEX($D$1:$D$28,SMALL(IF(($D$1:$D$28=P$8)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($B$1:$B$28)+1),1)))You can try this formula which returns the results in the intended layout. The formula is in cell H2 and filled across range H2:K5.
The formulas in cells E2 (filled down) and F2 (filled across range F2:G5) are in my first reply in this discussion.
All formulas must be entered with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.