Want multiple results from Vlookup

[Gallinski]

Hi all

I would like to obtain multiple results from a lookup of some sort. Please see attached.

Company A-G has multiple sales. When I change the company in the query outlined area i'd like to list all their sales by customer name and the amount. Not sure how to go about this as each comany will have different amounts of sales.

Thanks

[MarvinP]

Hi Gallinski,

I think a Pivot Table is what you need with a company filter. See attached.

[Gallinski]

your right, pivot table is ideal, but this is just a demo. the formula will actually be put into an invoice layout.

[MarvinP]

OK - and does the Pivot Table help then?

Create the invoice and grab cells from the Pivot Table to populate the invoice?

I guess I need to see the invoice layout to get the next step, if you still need help with it.?

[Gallinski]

Ok, I'm trying to automate the invoice, so as when I change the TO field on the invoice to the company name, it will auto populate the invoice below with the sales and amounts.

IE TO: Company A, Description and amount auto fill in with sales.

See new attachment.

[Gallinski]

i'm debating if an advanced filter might work?

ok this does not work as the advanced filter does not auto update.

[Gallinski]

Never mind. Found another thread with a base formula and modified to my needs.

{=IFERROR(INDEX($A$1:$F$30,SMALL(IF($C$2:$C$30=$H$2,ROW($A$2:$A$30),""),ROW($A1)),4),"")}