Performing a Case-Sensitive IF search

[lpa06432]

The relevant part of the array formula I'm working with (this is only half of it; the other half works fine)

=IFERROR(IF(B5=0, "", IFERROR(INDEX('.14a - JPM - Coll Plgd Stmts'!AW:AW+'.14a - JPM - Coll Plgd Stmts'!AV:AV+'.14a - JPM - Coll Plgd Stmts'!AD:AD, SMALL(IF('.14a - JPM - Coll Plgd Stmts'!C:C="Collateral / Credit Support Provided By JPMCB", ROW('.14a - JPM - Coll Plgd Stmts'!C:C)), ROWS('.14 - JPM - Collateral Pledged'!B$5:B5))), ""))*1, " ")

Here's the issue:

The spooled statement contains the phrase "Collateral / Credit Support Provided By JPMCB" twice for each account, in the same column. The first time, it is mixed case; the second time, it is in all caps.

I want to have the formula return the values when the "Collateral / Credit Support Provided By JPMCB" phrase is mixed case, but not when it is in all caps.

I believe I should be inserting an EXACT command in there somewhere, but I'm not really sure of the syntax.

[FlameRetired]

I can't tell much from what's posted. A sample Excel workbook upload (no pics or screenshots ... saves retyping data). Just a small sample is usually sufficient ... 20-30 rows.

If you are not familiar with how to upload an Excel file:


To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[FlameRetired]

Try in BL13 and filled down.

=IF(EXACT(C13,"Collateral / Credit Support Provided By JPMCB"),C13,"")

[lpa06432]

ok. desensitized sample attached, as requested. Thanks.

[FlameRetired]

In the meantime have you tried FIND? It is case sensitive.

In J1 and filled down.

=IFERROR(LOOKUP(1E+306,FIND(I1,$G$1:$G$30),$G$1:$G$30),"")

	G	H	I	J
1	Kelly		Bob
2	JOHN		John	John
3	Joe		Mike	Mike
4	ELSIE		Jacob	Jacob
5	Sandra		Kelly	Kelly
6	PAUL		Martha	Martha
7	JOANNE		Joe	Joe
8	JOANNE		Rachel
9	Mike		Liz
10	Linda		Elsie
11	Steve		JoAnne
12	RACHEL
13	BOB
14	JEANNINE
15	John
16	Martha
17	Vince
18	SUSAN
19	Sandra
20	JOHN
21	Phyllis
22	JEANNINE
23	LINDA
24	MARTHA
25	Jacob
26	JOANNE
27	JOE
28	JOHN
29	LIZ
30	Joe