tried wholeday to solve this problem. i need to match partial text from 'title' and meet a condition 'chn' column and bring value from the index column
the current formula im using couldnt add an additional condition to look into chn column, also the partial matching of text in some cases is not helpful
the current formula im using couldnt add an additional condition to look into chn column, also the partial matching of text in some cases is not helpful
help.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
2 | |||||||||||
3 | need to match title and chn and bring value from index column | ||||||||||
4 | Employee | =INDEX(F$7:F$11,MATCH(TRUE,ISNUMBER(SEARCH(B$6:B$13,B13)),0)) | |||||||||
5 | title | chn | expected result | current formula I have | |||||||
6 | Kaisi Ter | AAA | Kaisi | Kaisi | index value | chn | |||||
7 | Siyan | GGG | siyani | siyani | Kaisi | AAA | |||||
8 | Farq E | AAA | farq | farq | siyani | GGG | |||||
9 | Habs Ep | AAA | habs | habs | farq | AAA | |||||
10 | magar | HHH | agar | habs | AAA | ||||||
11 | agar t | GGG | agar | #REF! | agar | GGG | |||||
12 | Kaisi Teri | AAA | Kaisi | Kaisi | |||||||
13 | Kaisi Teri | GGG | Kaisi | ||||||||
14 | |||||||||||
INDEX-MATCH1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E6:E13 | E6 | =INDEX(F$7:F$11,MATCH(TRUE,ISNUMBER(SEARCH(B$6:B$13,B6)),0)) |