Vlookup with multiple matches

[MrBurn5]

I'm trying to cascade vlookups based off of one entry. I've been using Vlookup for a long time now and know it has a few drawbacks, and I'm hitting both of them with this example. I know it's not the proper formula to use so I'm looking for your help.

What I want to do is type in a quarterback's name in to A2. that then pulls that QB's data out of one matrix and sticks it on the second row. I then want to pull all of the intended receivers for that QB's team in rows 6-20, by matching their team to the quarterback's. In this case, I'm using Matthew Stafford from Detroit, and then looking for Detroit in the receivers matrix.

Problem 1: I know its possible, but I don't know how to search the second column in a vlookup, but still pull the players' names from the first column.

Problem 2: If I do a basic vlookup with multiple matches, it always displays the first one. Is there any way to have it go down the list and enter them in order?



Excel 2016 (Mac) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Matthew Stafford	Det	592	398	67.2	4262	7.2	32	5.4	13	2.2	57	44	251	97
3
4
5
6	Golden Tate	#REF!
7	Calvin Johnson
8
9
10
11
12
13	QUARTERBACKS
14	Player	Team	Att	Cmp	Pct	Yds	YPA	TD	TD%	Int	Int%	Lg	Sack	Loss	Rate
15	Cam Newton	Car	495	296	59.8	3837	7.8	35	7.1	10	2	74t	33	284	99.4
16	Tyrod Taylor	Buf	380	242	63.7	3035	8	20	5.3	6	1.6	63	36	212	99.4
17	Matthew Stafford	Det	592	398	67.2	4262	7.2	32	5.4	13	2.2	57	44	251	97
18	Alex Smith	KC	470	307	65.3	3486	7.4	20	4.3	7	1.5	80t	45	235	95.4
19
20
21	RECIEVERS
22	Player	Team	Gms	Rec	Yds	Avg	YPG	Lg	TD	FD	Tar	YAC
23	Demaryius Thomas	Den	16	105	1,304	12.42	81.5	72t	6	63	177	479
24	Odell Beckham Jr.	NYG	15	96	1,450	15.1	96.7	87t	13	67	158	590
25	Delanie Walker	Ten	15	94	1,088	11.57	72.5	61t	6	52	133	384
26	Golden Tate	Det	16	90	813	9.03	50.8	43	6	50	128	533
27	Calvin Johnson	Det	16	88	1,214	13.8	75.9	57	9	65	149	296
28	Jordan Reed	Was	14	87	952	10.94	68	32	11	54	114	468
29

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =VLOOKUP($A$2,$A$15:$O$18,2,FALSE) C2 =VLOOKUP($A$2,$A$15:$O$18,3,FALSE) D2 =VLOOKUP($A$2,$A$15:$O$18,4,FALSE) E2 =VLOOKUP($A$2,$A$15:$O$18,5,FALSE) F2 =VLOOKUP($A$2,$A$15:$O$18,6,FALSE) G2 =VLOOKUP($A$2,$A$15:$O$18,7,FALSE) H2 =VLOOKUP($A$2,$A$15:$O$18,8,FALSE) I2 =VLOOKUP($A$2,$A$15:$O$18,9,FALSE) J2 =VLOOKUP($A$2,$A$15:$O$18,10,FALSE) K2 =VLOOKUP($A$2,$A$15:$O$18,11,FALSE) L2 =VLOOKUP($A$2,$A$15:$O$18,12,FALSE) M2 =VLOOKUP($A$2,$A$15:$O$18,13,FALSE) N2 =VLOOKUP($A$2,$A$15:$O$18,14,FALSE) O2 =VLOOKUP($A$2,$A$15:$O$18,15,FALSE) B6 =INDEX(A23:L28,MATCH(B2,B23:B28,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[MrBurn5]

The players names were manually entered in A6 and A7. Ideally those auto populate.

 

[ORoxo]

Hello, Mr Burn
If the sheet with the Qb's names has the same header as the matrix you might use this:

=+INDEX($B$8:$E$10;MATCH($A2;$A$8:$A$10;0);MATCH(B$1;$B$7:$E$7;0))

Check my sheet below if you need:

https://drive.google.com/open?id=0B2SFnd3Fm3i-WUM1WXoyMUlkY2M