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:

[TABLE="class: outer_border, width: 300, align: left"]
<TBODY>[TR]
[TD]ACCOUNT
[/TD]
[TD]NAME
[/TD]
[TD]TICKER
[/TD]
[TD]WEIGHT
[/TD]
[/TR]
[TR]
[TD]1-ABC
[/TD]
[TD]1-ABC
[/TD]
[TD="align: center"](empty)[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]1-ABC
[/TD]
[TD]Microsoft
[/TD]
[TD]MSFT
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]1-ABC
[/TD]
[TD]IBM
[/TD]
[TD]IBM
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]2-BCD
[/TD]
[TD]2-BCD
[/TD]
[TD](empty)[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]2-BCD
[/TD]
[TD]Proctor & Gamble
[/TD]
[TD]PG
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]2-BCD
[/TD]
[TD]Microsoft
[/TD]
[TD]MSFT
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]2-BCD
[/TD]
[TD]IBM
[/TD]
[TD]IBM
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]3-CDE
[/TD]
[TD]3-CDE
[/TD]
[TD](empty)[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]3-CDE
[/TD]
[TD]Facebook
[/TD]
[TD]FB
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]3-CDE
[/TD]
[TD]IBM
[/TD]
[TD]IBM
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]3-CDE
[/TD]
[TD]Northeast Utilities
[/TD]
[TD]NU
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]3-CDE
[/TD]
[TD]Apple
[/TD]
[TD]AAPL
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]3-CDE
[/TD]
[TD]Google
[/TD]
[TD]GOOG
[/TD]
[TD]10
[/TD]
[/TR]
</TBODY>[/TABLE]
​

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.

[TABLE="class: outer_border, width: 350, align: left"]
<TBODY>[TR]
[TD]ACCOUNT[/TD]
[TD]AAPL[/TD]
[TD]FB[/TD]
[TD]GOOG[/TD]
[TD]IBM[/TD]
[TD]MSFT[/TD]
[TD]NU[/TD]
[TD]PG[/TD]
[/TR]
[TR]
[TD]1-ABC[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[/TR]
[TR]
[TD]2-BCD[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[/TR]
[TR]
[TD]3-CDE
[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[TD]Weight?[/TD]
[/TR]
</TBODY>[/TABLE]








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
ACCOUNT	NAME	TICKER	WEIGHT	ACCOUNT	AAPL	FB	GOOG	IBM	MSFT	NU	PG
1-ABC	1-ABC	1-ABC
1-ABC	Microsoft	MSFT	2-BCD
1-ABC	IBM	IBM	3-CDE
2-BCD	2-BCD
2-BCD	Proctor & Gamble	PG
2-BCD	Microsoft	MSFT
2-BCD	IBM	IBM
3-CDE	3-CDE
3-CDE	Facebook	FB
3-CDE	IBM	IBM
3-CDE	Northeast Utilities	NU
3-CDE	Apple	AAPL
3-CDE	Google	GOOG

<TBODY>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]50
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20
[/TD]
[TD="align: right"]20
[/TD]

[TD="align: right"]60
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]50
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]25
[/TD]

[TD="align: right"]25
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"]60
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]

[TD="align: right"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]

[TD="align: right"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]

[TD="align: right"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]

[TD="align: right"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]

[TD="align: right"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]

[TD="align: right"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]G2
[/TH]
[TD="align: left"]=IFERROR(INDEX($D$2:$D$14,MATCH(1,INDEX(($A$2:$A$14=$F2)*($C$2:$C$14=G$1),),FALSE)),"")
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]



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

 

[rgs694]

Perfect. Thank you Andrew.
-Rich