Need help on formula/vba code

Occasional Visitor

Hello guys,

Newbie/Noob here, need your hand on this one:

 ABCDE
1 H058B057C062H059
21-Nov18,000   
32-Nov2,0003,000  
43-Nov 20,00015,000 
54-Nov  10,00015,000

B1:E1 is my SW Code
B2:E5 are my quantity

need to do something like xlookup but don't know how to do it. Here's my question,
on another sheet, if my lookup value is on the Column A, how can I get its all quantity and SW Code using a formula or a VBA? Something like this:

Date:SW CodeQuantity
3-NovB05720,000
 C06215,000

 

3-Nov is my lookup value
B057 & C062 / 20,000 & 15,000 are my result on lookup

Thanks in advance!

1 Reply

@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.