 Contributor

# Need help on formula/vba code

Hello guys,

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

 A B C D E 1 H058 B057 C062 H059 2 1-Nov 18,000 3 2-Nov 2,000 3,000 4 3-Nov 20,000 15,000 5 4-Nov 10,000 15,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 Code Quantity 3-Nov B057 20,000 C062 15,000

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

6 Replies

# Re: Need help on formula/vba code

@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(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:

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

# Re: Need help on formula/vba code

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!

# Re: Need help on formula/vba code

and will update you as soon as possible! I PROMISE!

# Re: Need help on formula/vba code

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.

# Re: Need help on formula/vba code

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