VLOOKUP when specified value is repeated more than once

[Nick70]

Hi,

I have a range(A2:B10) where in range(A2:A10) we have clients (e.g. Client1, Client2 etc.) and in range(B2:B10) we have products (e.g. Product1, Product2 etc.).

If I do a VLOOKUP on Client2 for example

=VLOOKUP("Client2",$A$2:$B$10,2,FALSE)

I will get the product next to Client2.
But what if I have two Client2 for example in one in cell A4 and one in cell A5?
Which formulas can I use so that when I do a VLOOKUP (let's say in cell G2) on Client2 I first get value in cell B4 but then when I do a second VLOOKUP (let's say in cell G3) I get value in cell B5 (so I get all products linked to Client2)?
What is the best way to extract this information?
Is a Pivot the only way to do this?

Thanks,
Nic

 

[Eric W]

Try this:

Book1
	A	B	C	D	E
1	Client	Product		Client	Products
2	a	1		a	1
3	b	2			3
4	a	3			6
5	c	4
6	d	5
7	a	6
8	b	7
9
10
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=FILTER(B2:B10,A2:A10=D2)
Dynamic array formulas.

 

[Nick70]

Perfect!
That is exactly what I was looking for!
Txs,
Nic

 

[Eric W]

Happy to help!