Adding a filter to Vlookup

[CV12]

Hello,

I am trying to add a filter to a vlookup, but not figuring it out.
My input is 5, which corresponds to both 50 and 150 in col B. Is there any way, if my filter input is b, my result will be 150?

Book1
	A	B	C	D	E
1	1	10	a
2	2	20	a
3	3	30	a
4	4	40	a	5	a
5	5	50	a
6	6	60	a
7	7	70	a
8	8	80	a	50
9	9	90	a
10	10	100	a
11	1	110	b
12	2	120	b
13	3	130	b
14	4	140	b
15	5	150	b
16	6	160	b
17	7	170	b
18	8	180	b
19	9	190	b
20	10	200	b
Sheet1

Cell Formulas
Range	Formula
D8	D8	=VLOOKUP(D4,A1:B20,2)

Thanks a lot

 

[Fluff]

How about (for 365)

=FILTER(B1:B20,(A1:A20=D4)*(C1:C20=E4))

 

[CV12]

Thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[CV12]

In addition to this,

Was it not possible to use spilled data on a filter formula. I thought the filter function was one of the functions that could be used with spilled data?

Book1
	A	B	C	D	E	F	G	H	I
1	1	10	a				1	#N/A
2	2	20	a				2
3	3	30	a				3
4	4	40	a		b		4
5	5	50	a				5
6	6	60	a				6
7	7	70	a				7
8	8	80	a				8
9	9	90	a				9
10	10	100	a				10
11	1	110	b
12	2	120	b
13	3	130	b
14	4	140	b
15	5	150	b
16	6	160	b						1
17	7	170	b						2
18	8	180	b						3
19	9	190	b						4
20	10	200	b						5
21									6
22									7
23									8
24									9
25									#
26
Sheet1

Cell Formulas
Range	Formula
G1:G10	G1	=SORT(I16:I25)
H1	H1	=FILTER($B$1:$B$20,($A$1:$A$20=G1#)*($C$1:$C$20=$E$4))
Dynamic array formulas.

 

[Fluff]

You would need to use it like

=FILTER($B$1:$B$20,(ISNUMBER(MATCH($A$1:$A$20,G1#,0)))*($C$1:$C$20=$E$4))

 

[CV12]

Perfect, thanks a lot

 

[Fluff]

Glad to help & thanks for the feedback.