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.

 

[jkpieterse]

Why not use MATCH?

=MATCH(J6,Credit_Ratings,0)

 

[gcefaloni]

Why not use MATCH?

=MATCH(J6,Credit_Ratings,0)

Hi jkpieterse,

Because it returns a #N/A. Not sure if it's because it is an array with multiple columns instead of a single column. Or because there are multiple instances of the letter "A" being present in the table even if they are all in the same row. It's the first thing I tried but it doesn't seem to work. I don't know if it's useful but the "Credit_Ratings" name represents the entire credit ratings table of A1 to D22.

 

[steve the fish]

This should work:

=MIN(IF($A$2:$D$22="A",ROW($A$2:$D$22)))

CTRL-SHIFT-ENTER entry.

 

[gaz_chops]

Maybe

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

 

[gcefaloni]

This should work:

=MIN(IF($A$2:$D$22="A",ROW($A$2:$D$22)))

CTRL-SHIFT-ENTER entry.

This unfortunately also suffers from the problem of seeing the "A" being repeated in "AAA" or "AA" and therefore returns incorrect references.

 

[gaz_chops]

Using @steve the fish's solution, but tweaked to match exact (Case)

=MIN(IF(EXACT($A$2:$D$22,J6),ROW($A$2:$D$22)))

It appears to work for me

[TABLE="width: 922"]
<colgroup><col width="97" style="width: 73pt;"><col width="87" span="4" style="width: 65pt;"><col width="129" style="width: 97pt;"><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 97"]RTG_SP[/TD]
[TD="class: xl63, width: 87"]RTG_DBRS[/TD]
[TD="class: xl63, width: 87"]RTG_MOODY[/TD]
[TD="class: xl63, width: 87"]RTG_FITCH[/TD]
[TD="width: 87"][/TD]
[TD="width: 129"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl63"]AAA[/TD]
[TD="class: xl63"]AAA[/TD]
[TD="class: xl63"]Aaa[/TD]
[TD="class: xl63"]AAA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]AA+[/TD]
[TD="class: xl63"]AAH[/TD]
[TD="class: xl63"]Aa1[/TD]
[TD="class: xl63"]AA+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]AA[/TD]
[TD="class: xl63"]AA[/TD]
[TD="class: xl63"]Aa2[/TD]
[TD="class: xl63"]AA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]AA-[/TD]
[TD="class: xl63"]AAL[/TD]
[TD="class: xl63"]Aa3[/TD]
[TD="class: xl63"]AA-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]A+[/TD]
[TD="class: xl63"]AH[/TD]
[TD="class: xl63"]A1[/TD]
[TD="class: xl63"]A+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"]A2[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Row[/TD]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"]A-[/TD]
[TD="class: xl63"]AL[/TD]
[TD="class: xl63"]A3[/TD]
[TD="class: xl63"]A-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]BBB+[/TD]
[TD="class: xl63"]BBBH[/TD]
[TD="class: xl63"]Baa1[/TD]
[TD="class: xl63"]BBB+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]BBB[/TD]
[TD="class: xl63"]BBB[/TD]
[TD="class: xl63"]Baa2[/TD]
[TD="class: xl63"]BBB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]BBB-[/TD]
[TD="class: xl63"]BBBL[/TD]
[TD="class: xl63"]Baa3[/TD]
[TD="class: xl63"]BBB-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]BB+[/TD]
[TD="class: xl63"]BBH[/TD]
[TD="class: xl63"]Ba1[/TD]
[TD="class: xl63"]BB+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]BB[/TD]
[TD="class: xl63"]BB[/TD]
[TD="class: xl63"]Ba2[/TD]
[TD="class: xl63"]BB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]BB-[/TD]
[TD="class: xl63"]BBL[/TD]
[TD="class: xl63"]Ba3[/TD]
[TD="class: xl63"]BB-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]B+[/TD]
[TD="class: xl63"]BH[/TD]
[TD="class: xl63"]B1[/TD]
[TD="class: xl63"]B+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]B2[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]B-[/TD]
[TD="class: xl63"]BL[/TD]
[TD="class: xl63"]B3[/TD]
[TD="class: xl63"]B-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]CCC+[/TD]
[TD="class: xl63"]CCCH[/TD]
[TD="class: xl63"]Caa1[/TD]
[TD="class: xl63"]CCC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]CCC[/TD]
[TD="class: xl63"]CCC[/TD]
[TD="class: xl63"]Caa2[/TD]
[TD="class: xl63"]CCC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]CCC-[/TD]
[TD="class: xl63"]CCCL[/TD]
[TD="class: xl63"]Caa3[/TD]
[TD="class: xl63"]CCC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]CC[/TD]
[TD="class: xl63"]CC[/TD]
[TD="class: xl63"]Ca[/TD]
[TD="class: xl63"]DDD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]D[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63"]DD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gcefaloni]

Maybe

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

This seems to work! What do the "|" do? I had tried SEARCH(" "&J6&" "," "&Credit_Ratings&" ")) without avail.

Thanks!

 

[gaz_chops]

I'm just concatenating | with the look up value, so A becomes |A|, the array showing A becomes |A| whilst AAA becomes |AAA|.

Problem is it is not case sensitive, use Find instead of search.

And Steve's solution works for me and is much neater.

 

[gcefaloni]

Thanks a lot guys, both gaz_chops and steve the fish solutions seem to work after the adjustments!