Finding exact text string in an array

[gcefaloni]

Hi guys,

I have a question regarding the best way to find the row number of a cell containing an exact string which in my example is a single letter. The problem I'm encountering is that this letter also happens to be within words of other cells. How would I go about returning only the row with the exact letter as a text string. The following table should help understand the situation

I'm using the following formula to search the array below:

=INDEX(Credit_Ratings,SMALL(IF(NOT(ISERROR(SEARCH(J6,Credit_Ratings))),ROW($1:$22),99^99),1),2)

J6 cell contains only the letter "A". The problem is that this letter also happens to be within the string AAA, AA+, A+, etc. However, I want the formula to return row number 7, not row number 2 because it finds an "A" within the "AAA" string in row 2. How would I go about modifying the above formula to return only the row for the exact content of cell J6 and nothing else?

RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
AAA	AAA	Aaa	AAA
AA+	AAH	Aa1	AA+
AA	AA	Aa2	AA
AA-	AAL	Aa3	AA-
A+	AH	A1	A+
A	A	A2	A
A-	AL	A3	A-
BBB+	BBBH	Baa1	BBB+
BBB	BBB	Baa2	BBB
BBB-	BBBL	Baa3	BBB-
BB+	BBH	Ba1	BB+
BB	BB	Ba2	BB
BB-	BBL	Ba3	BB-
B+	BH	B1	B+
B	B	B2	B
B-	BL	B3	B-
CCC+	CCCH	Caa1	CCC
CCC	CCC	Caa2	CCC
CCC-	CCCL	Caa3	CCC
CC	CC	Ca	DDD
D	D	C	DD

<tbody>
</tbody>

Thank you very much in advance!

P.S. =SUMPRODUCT((Credit_Ratings=J6)*(ROW(Credit_Ratings))) is a different way to go about it, but I encounter the same type of problem.

 

[gaz_chops]

You're welcome.

 

[jkpieterse]

Aha, got it. You could also use something simple (no array formula) like:

=MIN(IFERROR(MATCH(J6,$A$2:$A$22,0),999),IFERROR(MATCH(J6,$B$2:$B$22,0),999),IFERROR(MATCH(J6,$C$2:$C$22,0),999),IFERROR(MATCH(J6,$D$2:$D$22,0),999))

 

[Aladin Akyurek]

Book1
	A	B	C	D	J
1	RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
2	AAA	AAA	Aaa	AAA
3	AA+	AAH	Aa1	AA+
4	AA	AA	Aa2	AA
5	AA-	AAL	Aa3	AA-
6	A+	AH	A1	A+	A
7	A	A	A2	A	7
8	A-	AL	A3	A-
9	BBB+	BBBH	Baa1	BBB+
10	BBB	BBB	Baa2	BBB
11	BBB-	BBBL	Baa3	BBB-
12	BB+	BBH	Ba1	BB+
13	BB	BB	Ba2	BB
14	BB-	BBL	Ba3	BB-
15	B+	BH	B1	B+
16	B	B	B2	B
17	B-	BL	B3	B-
18	CCC+	CCCH	Caa1	CCC
19	CCC	CCC	Caa2	CCC
20	CCC-	CCCL	Caa3	CCC
21	CC	CC	Ca	DDD
22	D	D	C	DD
Sheet1

In J7 control+shift+enter, not just enter:

=MIN(IF(MMULT(ISNUMBER(SEARCH(" "&J6&" "," "&$A$2:$D$22&" "))+0,TRANSPOSE(COLUMN($A$2:$D$22)^0)),ROW($A$2:$D$22)))