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?

[TABLE="width: 284"]
<tbody>[TR]
[TD]RTG_SP[/TD]
[TD]RTG_DBRS[/TD]
[TD]RTG_MOODY[/TD]
[TD]RTG_FITCH[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]AAA[/TD]
[TD]Aaa[/TD]
[TD]AAA[/TD]
[/TR]
[TR]
[TD]AA+[/TD]
[TD]AAH[/TD]
[TD]Aa1[/TD]
[TD]AA+[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]AA[/TD]
[TD]Aa2[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]AA-[/TD]
[TD]AAL[/TD]
[TD]Aa3[/TD]
[TD]AA-[/TD]
[/TR]
[TR]
[TD]A+[/TD]
[TD]AH[/TD]
[TD]A1[/TD]
[TD]A+[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[TD]A2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]A-[/TD]
[TD]AL[/TD]
[TD]A3[/TD]
[TD]A-[/TD]
[/TR]
[TR]
[TD]BBB+[/TD]
[TD]BBBH[/TD]
[TD]Baa1[/TD]
[TD]BBB+[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]BBB[/TD]
[TD]Baa2[/TD]
[TD]BBB[/TD]
[/TR]
[TR]
[TD]BBB-[/TD]
[TD]BBBL[/TD]
[TD]Baa3[/TD]
[TD]BBB-[/TD]
[/TR]
[TR]
[TD]BB+[/TD]
[TD]BBH[/TD]
[TD]Ba1[/TD]
[TD]BB+[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]BB[/TD]
[TD]Ba2[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]BB-[/TD]
[TD]BBL[/TD]
[TD]Ba3[/TD]
[TD]BB-[/TD]
[/TR]
[TR]
[TD]B+[/TD]
[TD]BH[/TD]
[TD]B1[/TD]
[TD]B+[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[TD]B2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]B-[/TD]
[TD]BL[/TD]
[TD]B3[/TD]
[TD]B-[/TD]
[/TR]
[TR]
[TD]CCC+[/TD]
[TD]CCCH[/TD]
[TD]Caa1[/TD]
[TD]CCC[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]CCC[/TD]
[TD]Caa2[/TD]
[TD]CCC[/TD]
[/TR]
[TR]
[TD]CCC-[/TD]
[TD]CCCL[/TD]
[TD]Caa3[/TD]
[TD]CCC[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CC[/TD]
[TD]Ca[/TD]
[TD]DDD[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]DD[/TD]
[/TR]
</tbody>[/TABLE]


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)))