Finding exact text string in an array

G

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
119
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.
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
jkpieterse said:
Why not use MATCH?
Code: 
=MATCH(J6,Credit_Ratings,0)
Click to expand...

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.
 
Upvote 0
Maybe

=INDEX(Credit_Ratings,SMALL(IF(NOT(ISERROR(SEARCH("|"&J6&"|","|"&Credit_Ratings&"|"))),ROW($1:$22),99^99),1),1)
 
Upvote 0
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

Code: 
[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]
 
Last edited:
Upvote 0
gaz_chops said:
Maybe

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

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

Thanks!
 
Upvote 0
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Formulas within Array Constants (INDEX MATCH within SMALL)
Replies
6
Views
800
Eric W
Eric W
G
  • Question Question
SUMIF with exact text string in an array as criteria
Replies
6
Views
1K
gcefaloni
G
A
  • Question Question
Find the lowest value within a row from 3 columns
Replies
5
Views
834
asuh
A
A
  • Question Question
Find minimum text value
Replies
8
Views
1K
asuh
A

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top