Formula help

[AussieVic]

Hi i am after a formula that will look up J:K in A:B and return result in L

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
2	Security	Portfolio	Amount							Security	Portfolio	Amount
3	PCABD1	SMPDAF	123.6							PCABD1	SMPDAF
4	TYHUTJ2	SMLKH	1236.6
5
6
7
Sheet3

 

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER

=INDEX($C$1:$C$6,MATCH(1,($A$1:$A$6=J2)*($B$1:$B$6=K2),0))

 

[Aladin Akyurek]

Hi i am after a formula that will look up J:K in A:B and return result in L

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
2	Security	Portfolio	Amount							Security	Portfolio	Amount
3	PCABD1	SMPDAF	123.6							PCABD1	SMPDAF
4	TYHUTJ2	SMLKH	1236.6
5
6
7
Sheet3

L3, control+shift+enter, not just enter:

=INDEX(?C?3:?C?4,MATCH(1,IF(?A?3:?A?4=J3,IF(?B?3:?B?4=K3,1)),0))


__________________
Post #4 from ?stanbul

 

[dcardno]

Assuming that a given security could be assigned to more than one portfolio, and could have a different value in each portfolio, the easiest way to do this is to insert a helper column at C (your values will move over to column D), with a formula that concatenates column A and B.

Put a Vlookup in Column L (what is now Col L - will be Col M after the insertion mentioned above) that looks up the concatenated values in the helper column and the values column - see below. Note - the helper column doesn't have to be visible

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Security	Portfolio	helper	Value							Security	Portfolio	Value
2	AAA	111	AAA111	34.48							AAA	222	81.49
3	AAA	222	AAA222	81.49							BBB	111	74.61
4	BBB	111	BBB111	74.61							BBB	333	42.72
5	BBB	222	BBB222	6.48
6	BBB	333	BBB333	42.72
7	CCC	222	CCC222	81.61
8	DDD	111	DDD111	24.51
9	EEE	333	EEE333	23.15
Sheet1

 

[p45cal]

in L3:
=SUMPRODUCT(($A$3:$A$20=$J$3)*($B$3:$B$20=$K$3),$C$3:$C$20)
though this will sum all the rows with the same Security/Portfolio combination, which is maybe what you want, otherwise, be sure that only one row has that combination in coliumns A:B.