sudarsan23
New Member
- Joined
- Apr 17, 2021
- Messages
- 1
- Office Version
- 365
- 2019
- Platform
- Windows
- MacOS
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
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))) |