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?
<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.
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.
Last edited: