Need help on formula/vba code

Steel Contributor

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!

6 Replies

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

Sorry for the laaaaate reply, sorry to bringing up the Xlookup function, but it is not currently working on my excel 2016, please if you still have the interest to help me, I will gladly appreciate it! Thanks!
and will update you as soon as possible! I PROMISE!

@Rr_ 

In B2 on the output sheet, as an array formula confirmed by pressing Ctrl+Shift+Enter:

=IFERROR(INDEX(Sheet1!$B$1:$E$1, MATCH(0, IF(INDEX(Sheet1!$B$2:$E$5, MATCH($A$2, Sheet1!$A$2:$A$5, 0), 0), COUNTIF($B$1:$B1, Sheet1!$B$1:$E$1), ""), 0)),"")

And in C2:

=IFERROR(INDEX(Sheet1!$B$2:$E$5, MATCH($A$2, Sheet1!$A$2:$A$5, 0), MATCH(B2, Sheet1!$B$1:$E$1, 0)),"")

Replace Sheet1 with the real name of the data sheet.

Fill down as many rows as there are SW codes.

@Hans Vogelaar 

Again, thank you so much!

But it seems the code for my B2 is not working for me. I try to re-create your formula, but could you explain what does "Match(0, " do? It giving me a 0 return. If you need the sample of my File, I will gladly share it thru PM. Thanks!

PS. Glad to have your formula on for my SW Code, try to do something based on your formula and it actually worked! Thanks again!

@Rr_ 

Please send a copy of your workbook in a PM.