vlookup but return all values

[ammartino44]

Hello. In sheet 1, I have in column A, a list of departments. There are repeat departments that are associated with different expenses (in column B). In Sheet 2, I want to have a lookup in A1 and then in A2 and below I want to capture all of the expenses associated with my lookup. How can I do this? Vlookup doesn't work because it just returns 1 value.

[Pete_UK]

It would help if you posted a sample workbook, so we can see how your data is arranged.

Pete

[newdoverman]

Here are a couple of simple examples.
1. By formula

This uses a value entered in B1 of sheet 2

Please Login or Register  to view this content.

2. Advanced Filter.

	A	B
30	Department	Expenses
31	>""
32
33
34
35	Department	Expenses
36	a	1