Combining Index, small, if, row

[Translationguy]

Hi All

I'm running into a bit of an issue I can't work out, granted I have a horrible cold right now...

I have this formula to find content in a sheet called "Test", it works perfectly until later (G94):
=INDEX(Test!F:F,SMALL(IF($AX$92=Test!E:E,IF(A150=Test!B:B,ROW(Test!E:E)-ROW(Test!E1))+1),2))

What I want it to do/does is, search the Test sheet for:

• English keyword in E
• "Portugal - BGP" in B
• When there's a match, return the result from column F (other languages' keywords)
  • In this instance, skip the first result and instead give me the 2nd result

This is working fine, until I start adding more languages underneath. Once I add the Spanish keywords, it no longer functions correctly. My mind is a bit foggy right now, but I can't understand why it works until more content is added under.

ANy help would be massively appreciated!

mrexcel.xlsx
	A	B	C	D	E	F	G	H
90	General information			Keywords			Keywords
91	Country	Column1	Column2	Keyword 1	Search Vol.	Keyword 2	Ref to keyword 1	Search Vol.
92	1. MASTER			Pressure booster	No Search Vol.	water pressure booster system		No Search Vol.
93	Spain - BGE			elevador de presión	10	sistema de aumento de la presión del agua	0	#N/A
94	Portugal - BGP			pressurizador de água	140	sistema de reforço de pressão de água	0	#N/A
Boosting

Cell Formulas
Range	Formula
E92,H92	E92	=IFERROR(VLOOKUP(D92,#REF!,3,FALSE),"No Search Vol.")
D93	D93	=INDEX(Test!A:H,MATCH(Boosting!D92&A93,Test!E:E&Test!B:B,0),6)
E93:E94	E93	=VLOOKUP(Boosting!D93,Test!F:G,2,FALSE)
F93	F93	=INDEX(Test!A:H,MATCH(Boosting!F92&A93,Test!E:E&Test!B:B,0),6)
G93	G93	=INDEX(Test!F:F,SMALL(IF(Test!E:E=$D$92,IF(A94=Test!B:B,ROW(Test!E:E)-ROW(Test!E1))+1),2))
H93:H94	H93	=VLOOKUP(Boosting!G93,Test!F:G,2,FALSE)
D94	D94	=INDEX(Test!A:H,MATCH(Boosting!D92&A94,Test!E:E&Test!B:B,0),6)
F94	F94	=INDEX(Test!A:H,MATCH(Boosting!F92&A94,Test!E:E&Test!B:B,0),6)
G94	G94	=INDEX(Test!F:F,SMALL(IF((Test!E:E=$D$92),IF(A94=Test!B:B,ROW(Test!E:E)-ROW(Test!E1))+1),2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F92	Expression	=F89="WU "	text	NO
F92	Expression	=F89="CBS"	text	NO
F92	Expression	=F89="IND"	text	NO
F92	Expression	=F89="n/a"	text	NO
F92	Expression	=F89="General"	text	NO
F92	Expression	=F89="DBS"	text	NO
D92	Expression	=D89="WU "	text	NO
D92	Expression	=D89="CBS"	text	NO
D92	Expression	=D89="IND"	text	NO
D92	Expression	=D89="n/a"	text	NO
D92	Expression	=D89="General"	text	NO
D92	Expression	=D89="DBS"	text	NO
G93:G94,I93:I94,AA93:AA94,AC93:AC94,AU93:AU94,AW93:AW94	Expression	=#REF!="WU "	text	NO
G93:G94,I93:I94,AA93:AA94,AC93:AC94,AU93:AU94,AW93:AW94	Expression	=#REF!="CBS"	text	NO
G93:G94,I93:I94,AA93:AA94,AC93:AC94,AU93:AU94,AW93:AW94	Expression	=#REF!="IND"	text	NO
G93:G94,I93:I94,AA93:AA94,AC93:AC94,AU93:AU94,AW93:AW94	Expression	=#REF!="n/a"	text	NO
G93:G94,I93:I94,AA93:AA94,AC93:AC94,AU93:AU94,AW93:AW94	Expression	=#REF!="General"	text	NO
G93:G94,I93:I94,AA93:AA94,AC93:AC94,AU93:AU94,AW93:AW94	Expression	=#REF!="DBS"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C93:C94	List	=Data!$A$1:$A$4

mrexcel.xlsx
	A	B	C	D	E	F	G	H
13	Index	Language	Group	Category	Seed Keywords	LANGUAGE Keywords	SV	EN Translation
14	286	Portugal - BGP	112	Boosting	Pressure booster	pressurizador de água	140	pressure booster
15	287	Portugal - BGP	112	Boosting	Pressure booster	pressurizador para torneira	10	faucet pressure booster
16	288	Portugal - BGP	112	Boosting	Pressure booster	pressurizador de água para chuveiro	10	shower water pressure booster
17	289	Portugal - BGP	112	Boosting	Pressure booster	pressurizador de água residencial	10	residential water pressure booster
18	290	Portugal - BGP	113	Boosting	water pressure booster system	sistema de reforço de pressão de água	0	water pressure booster system
19	291	Portugal - BGP	113	Boosting	water pressure booster system	sistema de pressurização de água	10	water pressure system
20	292	Portugal - BGP	113	Boosting	water pressure booster system	sistema de pressurização de água residencial	70	residential water pressure system
21	293	Portugal - BGP	113	Boosting	water pressure booster system	sistema de pressurização de água industrial	10	industrial water pressure system
22	945	Spain - BGE		Boosting	Pressure booster	elevador de presión	10	pressure booster set
23	946	Spain - BGE		Boosting	Pressure booster	Pressure booster	20	pressure booster
24	947	Spain - BGE		Boosting	Pressure booster	elevador de presion de agua	10	water pressure booster
25	948	Spain - BGE		Boosting	water pressure booster system	sistema de aumento de la presión del agua	0	water pressure booster system
26	949	Spain - BGE		Boosting	water pressure booster system	sistema para aumentar presion de agua	10	water pressure booster set
27	950	Spain - BGE		Boosting	water pressure booster system	aparato para aumentar la presion del agua	10	water pressure booster
Test

Cell Formulas
Range	Formula
E15:E17,D26:E27,D23:E24,E19:E21	E15	=E14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F28:F1048576,F1:F21	Cell Value	duplicates	text	NO
F13:F21	Cell Value	duplicates	text	NO
F14:F21	Cell Value	duplicates	text	NO
F14:F21	Cell Value	duplicates	text	NO
F14:F21	Cell Value	duplicates	text	NO
F14:F21	Cell Value	duplicates	text	NO
F14:F21	Cell Value	duplicates	text	NO
F14:F21	Cell Value	duplicates	text	NO
F22:F27	Cell Value	duplicates	text	NO
F22:F27	Cell Value	duplicates	text	NO

 

[Translationguy]

*bump*

 

[Translationguy]

*bump*

 

[StephenCrump]

You have G93: =INDEX(Test!F:F,SMALL(IF(Test!E:E=$D$92,IF(A94=Test!B:B,ROW(Test!E:E)-ROW(Test!E1))+1),2))

You need G93: =INDEX(Test!F:F,SMALL(IF(Test!E:E=$D$92,IF(A94=Test!B:B,ROW(Test!E:E)-ROW(Test!E1)+1)),2))

 

[Translationguy]

You have G93: =INDEX(Test!F:F,SMALL(IF(Test!E:E=$D$92,IF(A94=Test!B:B,ROW(Test!E:E)-ROW(Test!E1))+1),2))

You need G93: =INDEX(Test!F:F,SMALL(IF(Test!E:E=$D$92,IF(A94=Test!B:B,ROW(Test!E:E)-ROW(Test!E1)+1)),2))

Many thanks for this, I confirm that it works!

I suspected it might be something simple, this cold really sucks!