VLOOKUP or INDEX formula to return multiple values in the same column

[Raidon]

Hi everyone!

I would like insert a formula that will create a list of all the invoices of a specific supplier. I know that a VLOOKUP formula only shows the first match it finds, but I would like it to list all the invoices.

Please see the attachment if you are interested in helping me.

Any help on this matter would be greatly appreciated.

[jon.uk]

I'd like to know if there's a formulaic answer to this aswell - if I were to do this I'd write a macro to loop through the invoices and compile another list where values match.

[Pete_UK]

Do you want all invoice numbers, or just those from a particular month? If for one month, how would this be indicated in the Vendor Lookup sheet?

Pete

EDIT: Jon - it can be done with formulae, as I hope to demonstrate later.

[dwint]

Please see the attached file

You will need column helper to get row number of Invoices
1 column by 108 rows with Array Formula
=SMALL(IF(('Sep 2012'!G2:G109='Vendor Lookup'!B2)*('Sep 2012'!R2:R109<>"Paid"),ROW(1:108)),ROW(1:108))

then you must using Index to get the right data
=INDEX('Sep 2012'!$B$2:$B$109;'Vendor Lookup'!A26)

[Raidon]

Thank you for your attempt, dwint. However, the formula does not seem to work as soon as you change the supplier name. It only picks up the existing supplier.

Pete,

For simplicity sake, I'd like a formula for, let's say, only Octobers invoices. As soon as we have that under lockdown, we can expand the formula to include other months as well.
The end results in my mind should list all the invoices of the specific supplier with the following information relating to each invoice:

[*]Invoice number[*]Invoice date[*]Expected payment date[*]Gross amount[*]Currency[*]Status

Thank you for the help.

Raidon

[Fotis1991]

Hi

One way could be this one.

In A26 and copy down, this Array formula.

Please Login or Register  to view this content.

See if this works for you.

If yes, we can do for all months..

[icestationzbra]

do not use Merged Cells as they are inimical to formulaic operations. use Centred Across Selection instead.

another way, rendered less efficient due to use of INDIRECT, would be (see highlighted regions in Yellow):

[Pete_UK]

Okay, yet another one. I've used INDIRECT, but have avoided the array formulae by having a helper column (U) in each monthly sheet, and a new column A in the Vendor sheet.

I've also shown all 3 month's data, with up to 20 rows per month. You can use the filter drop-down in cell A25 (set to not equal to hypen) to bunch these up.

Hope this helps.

Pete

[Raidon]

Hi all,

Thanks Pete, icestationzbra and Fotis1991 for the help and providing three excellent different solutions. You all are clearly Excel experts. I only wish I could have thought of it myself .

Have a nice day

Raidon

[Raidon]

Hi all,

Thanks Pete, icestationzbra and Fotis1991 for the help and providing three excellent different solutions. You all are clearly Excel experts. I only wish I could have thought of it myself .

Have a nice day

Raidon

[Raidon]

Hi

One way could be this one.

In A26 and copy down, this Array formula.

Please Login or Register  to view this content.

See if this works for you.

If yes, we can do for all months..

Hi Fotis,

Clearly your formula works perfectly, but I've tried to analyse it and I dont quite understand how it works and why it works.

Do mind giving a breakdown of the formula, so that I can understand what each part of the formula is doing and why?

Many thanks

Raidon

[Fotis1991]

=INDEX('Sep 2012'!$B$2:$B$1000,SMALL(IF('Sep 2012'!$G$2:$G$1000='Vendor Lookup'!$B$2,ROW('Sep 2012'!$B$2:$B$1000)-1),ROW('Sep 2012'!B1)))

SMALL(IF('Sep 2012'!$G$2:$G$1000='Vendor Lookup'!$B$2,ROW('Sep 2012'!$B$2:$B$1000),ROW('Sep 2012'!B1

Looking for the first match of Vendor Lookup B2, in that range. As you copy down is looking for the second, third, e.tc.(Row(B2), Row(B3)....ETC.

-1, IS because your data in Sept2012, starts in the second row. If they start in third row, then will be -2. ETC.......