if value is >= x it should return Major in 1st row All in 2nd row if <= x it should return All

[sudarsan23]

Good Morning friends ,

I tried index match , index small if
I tried many ways to get the desired but I failed

I have attached my work sheet please help

major all.xlsx
	A	B	C	D	E	F	G
1	Region	Position			Region
2	Anantapur	Major			Anantapur	Major	#N/A
3	Anantapur	All			Anantapur	Major	#VALUE!
4	Chittoor	Major			Anantapur	Major	#VALUE!
5	Chittoor	All			Anantapur1	#N/A	#VALUE!
6	East Godavari	Major			Anantapur	Major	#VALUE!
7	East Godavari	all			Anantapur	Major	#VALUE!
8	Krishna	Major			Anantapur	Major	#VALUE!
9	Krishna	Major			Chittoor	Major	#VALUE!
10	Kurnool	Major			Chittoor		#VALUE!
11	Kurnool	all			Chittoor		#VALUE!
12	Nellore	Major			Chittoor		#VALUE!
13	Nellore	all			Chittoor		#VALUE!
14	Prakasam	Major			Chittoor
15	Prakasam	all			Chittoor
16	Srikakulam	Major			Chittoor
17	Srikakulam	all			Chittoor
18	Kurnool	all			East Godavari
19	Nellore	Major			East Godavari
20	Nellore	all			East Godavari
21	Prakasam	Major			East Godavari
22	Prakasam	all			Guntur
23	Srikakulam	Major			Guntur
24	Srikakulam	all			Guntur
Sheet1

Cell Formulas
Range	Formula
F1	F1	=IFERROR(INDEX($B$3:$B$9, SMALL(IF(E$2=$A$3:$A$9, ROW($B$3:$B$9)-2,""), ROW()-2)),"")
F2:F9	F2	=INDEX($B$2:$B$24,MATCH(E2,$A$2:$A$23,0))
G2	G2	=INDEX($B$2:$B$24, SMALL(IF($A$2:$A$24=$E2,ROW($E$2:$E$31)-MIN(ROW($E$2:$E$31))+1, ""), COUNTIF(A$1:$A1, A1)))
G3:G13	G3	=INDEX($B$2:$B$24, SMALL(IF($A$2:$A$24=$E$1:$E$31*2, ROW($E$1:$E$31)-MIN(ROW($E$1:$E$31))+1, ""), COUNTIF(A$1:$A2, A2)))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to get two different names with same value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.