Referencing all non-blank values in a row/column

Hello Microsoft Community,   I've been building up a large worksheet that has several sheets using functions such as XLOOKUP & VLOOKUP from a series of tables. I expect to use this sheet for severa...