A formula to lookup data, when column data repeats itself.

[rgs694]

Hello all,
I've been using Excel for years for basic stuff, but only with formulas for the last year. I feel my problem is certainly solvable, based on all I've learned with formulas such as Index, Match, VLookup, etc. However, I just can't seem to nail this one.
The main set of data that I'm looking to reference using another table is contained in 4 columns, as such:

ACCOUNT	NAME	TICKER	WEIGHT
1-ABC	1-ABC	(empty)	(empty)
1-ABC	Microsoft	MSFT	50
1-ABC	IBM	IBM	50
2-BCD	2-BCD	(empty)	(empty)
2-BCD	Proctor & Gamble	PG	60
2-BCD	Microsoft	MSFT	20
2-BCD	IBM	IBM	20
3-CDE	3-CDE	(empty)	(empty)
3-CDE	Facebook	FB	25
3-CDE	IBM	IBM	25
3-CDE	Northeast Utilities	NU	25
3-CDE	Apple	AAPL	15
3-CDE	Google	GOOG	10

<TBODY>
</TBODY>

One note on this table, it's not completely symetrical in that the account number from column-1 alos appears in the first row of column-2, before the column-2 (NAME) data starts. This table represents a list of accounts, their stock holdings, ticker symbols, and weighting of stocks in each account. Each account can hold a slightly different number of holdings.

What I've done is I've created another table where the first column uses a formula to extract a unique list of these account numbers. Across the top of the table, I have the complete list of all stocks across all portfolios.

ACCOUNT	AAPL	FB	GOOG	IBM	MSFT	NU	PG
1-ABC	Weight?	Weight?	Weight?	Weight?	Weight?	Weight?	Weight?
2-BCD	Weight?	Weight?	Weight?	Weight?	Weight?	Weight?	Weight?
3-CDE	Weight?	Weight?	Weight?	Weight?	Weight?	Weight?	Weight?

<TBODY>
</TBODY>

My question is, how do I set up a formula in this second table that will look for the Weight of each holding, in each account from the first table. I'm struggling with looking up the data using account number, because the account numbers repeat along the first column of the first table. Any input would be much appreciated. Thank you, -Rich
​

 

[rgs694]

I apologize for the vast spacing... my first post. I may have a browser compatibility issue.

 

[Andrew Poulsom]

Example (formula in G2 copied down and across):

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ACCOUNT	NAME	TICKER	WEIGHT		ACCOUNT	AAPL	FB	GOOG	IBM	MSFT	NU	PG
2	1-ABC	1-ABC				1-ABC				50	50
3	1-ABC	Microsoft	MSFT	50		2-BCD				20	20		60
4	1-ABC	IBM	IBM	50		3-CDE	15	25	10	25		25
5	2-BCD	2-BCD
6	2-BCD	Proctor & Gamble	PG	60
7	2-BCD	Microsoft	MSFT	20
8	2-BCD	IBM	IBM	20
9	3-CDE	3-CDE
10	3-CDE	Facebook	FB	25
11	3-CDE	IBM	IBM	25
12	3-CDE	Northeast Utilities	NU	25
13	3-CDE	Apple	AAPL	15
14	3-CDE	Google	GOOG	10

<TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula G2 =IFERROR(INDEX($D$2:$D$14,MATCH(1,INDEX(($A$2:$A$14=$F2)*($C$2:$C$14=G$1),),FALSE)),"") <TBODY> </TBODY>

<TBODY>
</TBODY>

If you don't have Excel 2007 or above the formula will need amending.

 

[rgs694]

Perfect. Thank you Andrew.
-Rich