@rrestrivera In my code, Sheet1 is assumed to be the worksheet containing the data you posted; on the other sheet, cell A7 is assumed to have the lookup value.
XLOOKUP will readily get your (quantity) data on the same row as your lookup value:
=XLOOKUP(A7, Sheet1!$A$2:$A$5, Sheet1!$B$2:$E$5, "Date not found", 0, 1)
...but that alone does no filtering. While I believe a FILTER function can be used on the result of an XLOOKUP to ignore the "empty quantities", I can't at this time think of how to do that without restricting the range to one row ... or how to apply the same filtering of columns to a row outside the lookup range; maybe someone else can post a solution encompassing all that in one formula
In my simple solution, I use three* formulas: one using a MATCH function (to derive the row number of the desired quantity data, for use in the other two formulas), and two using combinations of FILTER with the MATCH result and the INDIRECT function (to construct references to the two different column ranges for SW Codes and for quantities; the quantities formula also uses XLOOKUP):
for the determination of row number, I put this formula into B7:
=MATCH(A7, Sheet1!$A$2:$A$5) + 1
...and referred to it in the following formulas...
for the determination of filtered SW Codes:
=FILTER( Sheet1!$B$1:$E$1, INDIRECT("Sheet1!$B$"&B7&":$E$"&B7)<>"" )
and for the determination of filtered quantities:
=FILTER( XLOOKUP(A7, Sheet1!$A$2:$A$5, Sheet1!$B$2:$E$5, "Date not found", 0, 1), INDIRECT("Sheet1!$B$"&B7&":$E$"&B7)<>"" )
* Using three formulas offers clarity. But once you understand the techniques, and if your version of Excel supports the LET function, you can use that function to reduce the number of formulas to two. Or if you don't use LET, you could write two messier formulas by embedding the MATCH formula in the other two.
