V Look Up / X Look Up subject to conditions

[ap_terminator]

Hi All,

I'm trying to pull data out subject to certain conditions, I have attached a simple version of what I'm actually doing. I need to fill in the yellow highlighted area, pulling out the correct amount for each country which has a EUR, GBP or USD FX rate.

For example in Column G i need a total of EUR 320,500 (A - 200,000, D - 76,000 , C - 44,500 with the rest of the cells in column G being 0). Note some of the countries in column A repeat but with a different FX rate e.g. country A.

Thanks.

 

[Eric W]

The problem with your layout is that for countries that appear more than once (A for example), you'd end up looking up some values more than once. In this case G3 would be the intersection of A and EUR, so it would look up 200,000. But G10 is also the intersection of A and EUR, so it would look up 200,000 again. (See Table 2 below.) So we need a way to find unique values. I came up with 2 methods. Table3 consists of 3 formulas, all the same, but they find the countries with a given currency and just include those in each column. Table 4 I like a bit better. Each country is listed just once, but the grid has each currency. That takes just 2 formulas, one for the unique country list, one for the totals.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Table 1					Table 2					Table 3							Table 4
2	Country	Currency	Amount			Country	EUR	GBP	USD		EUR		GBP		USD				EUR	GBP	USD
3	A	EUR	200000			A	200000	1000000	0		A	200000	B	456878	C	34998		A	200000	1000000	0
4	B	GBP	456878			B	0	456878	16000		D	76000	E	12889	B	16000		B	0	456878	16000
5	C	USD	34998			C	44500	0	34998		C	44500	F	100000				C	44500	0	34998
6	D	EUR	76000			D	76000	0	0				A	1000000				D	76000	0	0
7	E	GBP	12889			E	0	12889	0									E	0	12889	0
8	F	GBP	100000			F	0	100000	0									F	0	100000	0
9	A	GBP	1000000			A	200000	1000000	0
10	B	USD	16000			B	0	456878	16000
11	C	EUR	44500			C	44500	0	34998
12
13						Total	565000	3026645	101996			320500		1569767		50998			320500	1569767	50998
Sheet6

Cell Formulas
Range	Formula
G3:I11	G3	=SUMIFS($C:$C,$A:$A,$F3,$B:$B,G$2)
K3:L5,O3:P4,M3:N6	K3	=FILTER(FILTER($A$3:$C$11,$B$3:$B$11=K2),{1,0,1})
R3:R8	R3	=SORT(UNIQUE(A3:A11))
S3:U8	S3	=SUMIFS(C:C,A:A,R3#,B:B,S2:U2)
G13:I13,S13:U13,P13,N13,L13	G13	=SUM(G3:G11)
Dynamic array formulas.

Let us know if any of these work for you.