Problem with CSE formula

[pschepeler]

I'm having trouble with a CSE formula.
I have a worksheet ([EXPORTS]EXPORTS) that has several data relating to foreign trade... and I need to make a sheet for each country (TABLE A) involved showing only some of that data.

The thing's like this: I need to compare three cells (Country code, year, commodity code) from TABLE A with three ranges from[EXPORTS]EXPORTS with all the data, and return a value from the corresponding fourth range (commodity value).

I made a CSE formula that looks like this:
{=IF([EXPORTS]'EXPORTS!$A$2:$A$6700='TABLE A'!$A$1,IF([EXPORTS]'EXPORTS'!$C$2:$C$6700 ='TABLE A'!E$5,IF([EXPORTS]'EXPORTS'!$D$2:$D$6700='TABLE A'!$U8,[EXPORTS]'EXPORTS'!$L$2:$L$6700,"..."),"..."),"...")}

On the country sheet (TABLE A), the years go in the E row, the commodity codes on the U column, and the country code on the A1 cell.

The idea is to change the country code to get the corresponding data.

I always get the "...", so i never get a TRUE value from the IFs. I just can't get it to work. PLEASE HELP.

 

[Jim North]

I'm trying to get a picture... is this what your spreadsheets look like:

EXPORTS:

exports.xls
	B	C	D	E	F	G	H	I	J	K	L	M
1		USA		2002							GOLD
2		UK		2001							SILVER
3
4
EXPORTS

Table A:

exports.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	UK				2001																SILVER
2
3
Table A

What are you accumulating? On which sheet? in what column?

 

[PaddyD]

Looks like you are trying to do a multikey lookup. See here:

http://www.mrexcel.com/board/viewtopic.php?topic=16933&forum=2

for a few ideas & post back...

Paddy