What's wrong with this formula?

[Bobo3370]

=VLOOKUP(A2;IFS(C2="11";Baza_Aktivni_Instrastat_formula!A:H;IFormula!C2="12";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="FCA";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="EXW";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="DAP";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="CPT";Baza_Aktivni_Instrastat_formula!A:H;Formula!E2="1";Baza_Aktivni_Instrastat_formula!A:H;Formula!E2="2";Baza_Aktivni_Instrastat_formula!A:H;Formula!F2="3";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="BE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="CZ";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="DE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="GB";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="HU";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="IT";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="NL";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="PL";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="SE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="SI";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BD";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BG";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CH";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CZ";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="DE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="ES";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="FR";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="GB";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="HU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IT";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="JP";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="KG";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="KR";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="MM";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="MX";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="NL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="PK";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="PL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="RO";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="RU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SI";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SK";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TH";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TW";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="US";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="VN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="XU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="ZA";Baza_Aktivni_Instrastat_formula!A:H);2;FALSE)

 

[Peter_SSs]

So, did you try my suggested formula from post #6. It seems to produce exactly those results?
(apart from yours being upper case, unlike the yellow table they come from )

 

[Bobo3370]

I have copy paste it but it doesn't do anything. Maybe I am doing something wrong. Please help Peter.

Knjiga1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	MATERIAL	DESCRIPTION	VP	UI		VPr	ZN	ORIGIN							MATERIAL	DESCRIPTION	VP	UI		VPr	ZN	ORIGIN
2	84311000	=INDEX(P:P,AGGREGATE(15,6,ROW(P$2:P$23)/((O$2:O$23=A2)*(Q$2:Q$23=C2)*(R$2:R$23=D2)*(S$2:S$23=E2)*(T$2:T$23=F2)*(U$2:U$23=G2)*(V$2:V$23=H2)),1))	11	CPT	1	3	BE	TW							84311000	kuka	11	FCA	1	3	DE	DE
3	84311000		11	EXW	2	3	NL	NL							84311000	adapter	11	FCA	1	3	BE	US
4	84314980		11	DAP	1	3	HU	DE							84311000	prsten	11	CPT	1	3	BE	TW
5	84483900		11	EXW	2	3	GB	TW							84311000	prsten	11	FCA	1	3	SE	SE
6	84661020		11	FCA	1	3	SE	SE							84311000	držač iglica	11	EXW	2	3	NL	NL
7	84661020		11	EXW	2	3	GB	GB							84311000	držač iglica	11	CPT	1	3	DE	US
8	84679200		11	CPT	1	3	DE	CN							84314980	pokretna rola	11	DAP	1	3	HU	DE
9	84679200		11	FCA	1	3	SE	SE							84314980	pokretna rola	11	DAP	1	3	DE	DE
10															84483900	pribor	11	EXW	2	3	GB	TW
11															84661020	Držać alata-adapter	11	FCA	1	3	SE	SE
12															84661020	Držać alata-trn	11	EXW	2	3	GB	GB
13															84661020	Držać alata-trn	11	FCA	1	3	DE	GB
14															84679200	prigušivač	11	CPT	1	3	BE	IE
15															84679200	odstojnik	11	FCA	1	3	BE	ZA
16															84679200	odstojna cijev	11	CPT	1	3	BE	FR
17															84679200	matica	11	FCA	1	3	SE	CN
18															84679200	ploča za polirku	11	FCA	1	3	GB	GB
19															84679200	zadnja ploča	11	CPT	1	3	DE	CN
20															84679200	čahura	11	FCA	1	3	SE	SE
21															84679200	lopatice	11	CPT	1	3	BE	XU
22															84679200	lopatice	11	CPT	1	3	BE	ZA
23															84679200	poluga	11	EXW	2	3	DE	US
List1

 

[Peter_SSs]

I have copy paste it but it doesn't do anything. Maybe I am doing something wrong. Please help Peter.

It looks like your column B might be formatted as Text. Format the column to General and then enter the formula again.

 

[Bobo3370]

It works, awesome, thank You very much Peter. Can You please explain to me what does the number 15 and number 6 represent in the formula?
=INDEX(P:P,AGGREGATE(15,6,ROW(P$2:P$23)/((O$2:O$23=A2)*(Q$2:Q$23=C2)*(R$2:R$23=D2)*(S$2:S$23=E2)*(T$2:T$23=F2)*(U$2:U$23=G2)*(V$2:V$23=H2)),1))

 

[Peter_SSs]

It works, awesome, thank You very much Peter.

You're welcome. Glad we could help.

Can You please explain to me what does the number 15 and number 6 represent in the formula?

15 means SMALL and 6 means 'ignore error values'. You can find more information here

 

[Bobo3370]

Ok, I have put the formula that I need and this is what I get.
The grey table is in sheet 1 "Formula" and the yellow table is in sheet 2 "Baza_Aktivni_Instrast_formula"

Sheet 1 "Formula"

Intrastat_Obrazac_prije sumiranja.xls
	A	B	C	D	E	F	G	H
1	Šifra robe	Opis robe	Vrsta posla (obje znamenke)	Uvjeti isporuke		Vrsta prometa	Zemlja namjene/ isporuke	Zemlja podrijetla
2	39239000	#NAME?	11	FCA	1	3	DE	CN
Formula

Cell Formulas
Range	Formula
B2	B2	=INDEKS(Baza_Aktivni_Instrastat_formula!B:B,AGGREGATE(2,10,ROW(Baza_Aktivni_Instrastat_formula!B$2:B$541)/((Baza_Aktivni_Instrastat_formula!A$2:A$541=Formula!A2)*(Baza_Aktivni_Instrastat_formula!C$2:C$541=Formula!C2)*(Baza_Aktivni_Instrastat_formula!D$2:D$541=Formula!D2)*(Baza_Aktivni_Instrastat_formula!E$2:E$541=Formula!E2)*(Baza_Aktivni_Instrastat_formula!F$2:F$541=Formula!F2)*(Baza_Aktivni_Instrastat_formula!G$2:G$541=Formula!G2)*(Baza_Aktivni_Instrastat_formula!H$2:H$541=Formula!H2)),1))

Sheet 2 "Baza_Aktivni_Instrast_formula"

Intrastat_Obrazac_prije sumiranja.xls
	A	B	C	D	E	F	G	H
1	ŠIFRA ROBE	OPIS ROBE	VRSTA POSLA	UVIJETI ISPORUKE		VRSTA PROMETA	ZEMLJA NAMJENE / ISPORUKE	ZEMLJA PODRIJETLA
32	39239000	Umetak za kutije	11	FCA	1	3	DE	CN
Baza_Aktivni_Instrastat_formula

 

[Peter_SSs]

Looks like you might be using a non-English version of Excel and you have translated some function names to your language version (eg INDEX/INDEKS), but not AGGREGATE. You need to translate that too.

Also, it is a bad idea to use the sheet name of the sheet the formula is on. This can lead to hidden errors in some circumstances. So. since your formula is on the 'Formula' sheet you should get rid od Formula! wherever it is used to reference any cell or range on that sheet. I have marked a few of them, but not all, here:

 

[Bobo3370]

I have not noticed that. Thank You. I have changed it all to English bc I use Croatian version of Excel but still I do not get the result.
Now it shows me the error #Value!. Do You know what is the problem now?

Intrastat_Obrazac_prije sumiranja.xls
	A	B	C	D	E	F	G	H
1	Šifra robe	Opis robe	Vrsta posla (obje znamenke)	Uvjeti isporuke		Vrsta prometa	Zemlja namjene/ isporuke	Zemlja podrijetla
2	39239000	#VALUE!	11	FCA	1	3	DE	CN
Formula

Cell Formulas
Range	Formula
B2	B2	=INDEX(Baza_Aktivni_Instrastat_formula!B:B,AGGREGATE(2,10,ROW(Baza_Aktivni_Instrastat_formula!B$2:B$541)/((Baza_Aktivni_Instrastat_formula!A$2:A$541=A2)*(Baza_Aktivni_Instrastat_formula!C$2:C$541=C2)*(Baza_Aktivni_Instrastat_formula!D$2:D$541=D2)*(Baza_Aktivni_Instrastat_formula!E$2:E$541=E2)*(Baza_Aktivni_Instrastat_formula!F$2:F$541=F2)*(Baza_Aktivni_Instrastat_formula!G$2:G$541=G2)*(Baza_Aktivni_Instrastat_formula!H$2:H$541=H2)),1))

 

[Peter_SSs]

I missed this before, but you have also changed the first two arguments in the AGGREGATE function from 15 and 6 to 2 and 10. Why did you do that?

My formula:

Your formula:

 

[Bobo3370]

I do not know why did I do that. I forgot to change it after you told me what are this numbers. Now it is working and thank you a lot Peter, you really helped me.