Vlookup if more than 1 result

[Dazzybeeguy]

If I have a vlookup that in say Cell E2 looks for the Ref i.e A1234 and I want the 2nd column as my result.
The ref A1234 appears 4 times so I want formulas in Cell E3, E4 & E5
Cell E2 Formula =VLOOKUP("A1234",A1:B10,2,FALSE)

How do I get all four results and not the first result 4 times in cells E3:E5

So I want to get the following
E2 = Dig, E3 = Paint, E4 = Iron, E5 = Hoover

Reference (Column A)	Task (Column B)
A1234	Dig
A1235	D
A1234	Paint
A1237	A
A1238	C
A1234	Iron
A1240	A
A1244	C
A1234	Hoover

 

[etaf]

if it as described
=FILTER(B2:B10,A2:A10="A1234")
on the 365 version of excel - NOT the 2010 version

Book6
	A	B	C	D	E
1	Reference (Column A)	Task (Column B)
2	A1234	Dig			Dig
3	A1235	D			Paint
4	A1234	Paint			Iron
5	A1237	A			Hoover
6	A1238	C
7	A1234	Iron
8	A1240	A
9	A1244	C
10	A1234	Hoover
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(B2:B10,A2:A10="A1234")
Dynamic array formulas.

 

[Dazzybeeguy]

if it as described
=FILTER(B2:B10,A2:A10="A1234")
on the 365 version of excel - NOT the 2010 version

Book6
	A	B	C	D	E
1	Reference (Column A)	Task (Column B)
2	A1234	Dig			Dig
3	A1235	D			Paint
4	A1234	Paint			Iron
5	A1237	A			Hoover
6	A1238	C
7	A1234	Iron
8	A1240	A
9	A1244	C
10	A1234	Hoover
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(B2:B10,A2:A10="A1234")
Dynamic array formulas.

Thanks that works great, just wondering though, if I also wanted it to look at column C for a Y or a N and only return the results if it had a Y, what would the formula be ?

Thanks

 

[etaf]

=FILTER(B2:B10,(A2:A10="A1234")*(C2:C10="Y"))

Book6
	A	B	C	D	E
1	Reference (Column A)	Task (Column B)
2	A1234	Dig	Y		Dig
3	A1235	D	n		Hoover
4	A1234	Paint	n
5	A1237	A	y
6	A1238	C	y
7	A1234	Iron	n
8	A1240	A	n
9	A1244	C	n
10	A1234	Hoover	y
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=FILTER(B2:B10,(A2:A10="A1234")*(C2:C10="Y"))
Dynamic array formulas.

 

[Dazzybeeguy]

=FILTER(B2:B10,(A2:A10="A1234")*(C2:C10="Y"))

Book6
	A	B	C	D	E
1	Reference (Column A)	Task (Column B)
2	A1234	Dig	Y		Dig
3	A1235	D	n		Hoover
4	A1234	Paint	n
5	A1237	A	y
6	A1238	C	y
7	A1234	Iron	n
8	A1240	A	n
9	A1244	C	n
10	A1234	Hoover	y
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=FILTER(B2:B10,(A2:A10="A1234")*(C2:C10="Y"))
Dynamic array formulas.

Thank you

 

[etaf]

you are welcome

 

[Dazzybeeguy]

you are welcome

One last thing, is there a way to sort the columns so the populated ones are at the top

 

[etaf]

not sure i understand Populated ?

 

[Dazzybeeguy]

not sure i understand Populated ?

So if its not A Y the next one that has a Y displays at the top, so some sort of Sort feature is needed

 

[etaf]

you can use sort
=SORT(FILTER(B2:B10,(A2:A10="A1234")*(C2:C10="Y")))

but the items should appear in the order on the main column

so not sure why you are seeing

the next one that has a Y displays at the top,

see below i changed the Y

Book6
	A	B	C	D	E
1	Reference (Column A)	Task (Column B)
2	A1234	Dig	n		Paint
3	A1235	D	n		Hoover
4	A1234	Paint	y
5	A1237	A	y
6	A1238	C	y
7	A1234	Iron	n
8	A1240	A	n
9	A1244	C	n
10	A1234	Hoover	y
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=FILTER(B2:B10,(A2:A10="A1234")*(C2:C10="Y"))
Dynamic array formulas.