Adding functionality to a formula to catch case sensitive issues

[Peptide]

Hello - I have the formula below but want to add ability to catch if B7 is not an exact match due to letters being lower or capital. i.e. ABcDE would not match ABCDE.

I tried to add TRUE and EXACT terminology into the formula but could not make it work or I had it wrong somehow... ugh.

Any help would be greatly appreciated!

=IFERROR(VLOOKUP(B7,'Pricing Table'!$AY$5:$BM$36,MATCH(B87,'Pricing Table'!$AY$4:$BM$4,0),FALSE),"N-Term/Mgs Combo Not Found On N-Term Data Table")

 

[Eric W]

Try:

=IFERROR(INDEX('Pricing Table'!$AZ$5:$BM$36,MATCH(TRUE,EXACT(B7,'Pricing Table'!$AY$5:$AY$36),0),MATCH(B87,'Pricing Table'!$AZ$4:$BM$4,0)),"N-Term/Mgs Combo Not Found On N-Term Data Table")

and confirm it with Control+Shift+Enter.


Edit: This one doesn't require the CSE:

=IFERROR(INDEX('Pricing Table'!$AZ$5:$BM$36,LOOKUP(2,1/EXACT(B7,'Pricing Table'!$AY$5:$AY$36),ROW($AY$5:$AY$36)-ROW($AY$5)+1),MATCH(B87,'Pricing Table'!$AZ$4:$BM$4,0)),"N-Term/Mgs Combo Not Found On N-Term Data Table")

 

[Peptide]

Hi Eric and thanks for the reply.

I tried both options but did not get it to work so I am guessing I may not have been 100% clear - sorry

Example
- if it looks for B7 as ABcDE and does not find it, it replies "N-Term/Mgs Combo Not Found On N-Term Data Table"
- if it looks for B7 as ABcDE and finds ABCDE, it again replies "N-Term/Mgs Combo Not Found On N-Term Data Table"
- if it looks for B7 as ABcDE, and finds the all cap match, it does the rest of the calculation

This make sense?

 

[Eric W]

That's how I understood it. Here's my test sheet:

	A	B	C	AX	AY	AZ	BA	BB	BC	BD	BE
BA6
BA6
abcde	AZ5	BA5	BB5	BC5	BD5	BE5
ABcDE	AZ6	BA6	BB6	BC6	BD6	BE6
ABcDE	ABcdE	AZ7	BA7	BB7	BC7	BD7	BE7
abcdE	AZ8	BA8	BB8	BC8	BD8	BE8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]86[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Pricing Table​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IFERROR(INDEX('Pricing Table'!$AZ$5:$BM$36,LOOKUP(2,1/EXACT(B7,'Pricing Table'!$AY$5:$AY$36),ROW($AY$5:$AY$36)-ROW($AY$5)+1),MATCH(B87,'Pricing Table'!$AZ$4:$BM$4,0)),"N-Term/Mgs Combo Not Found On N-Term Data Table")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]{=IFERROR(INDEX('Pricing Table'!$AZ$5:$BM$36,MATCH(TRUE,EXACT(B7,'Pricing Table'!$AY$5:$AY$36),0),MATCH(B87,'Pricing Table'!$AZ$4:$BM$4,0)),"N-Term/Mgs Combo Not Found On N-Term Data Table")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




For convenience, I put the formula, parameters, and table on the same sheet. But it should be easy enough to see that it would work on different sheets. Notice that the formulas return BA6, which is the value at the intersection of the 3 column (from B87), and the ABcDE row (from B7). Notice how it skipped the "abcde" row? If you put in ABCDE in B7, it will give you the error message.

If this is not what you want, let me know. If your sheet is set up differently, let me know.

 

[Peptide]

Note I updated the last scenario..


Hi Eric and thanks for the reply.

I tried both options but did not get it to work so I am guessing I may not have been 100% clear - sorry

Example
- if it looks for B7 as ABcDE and does not find it, it replies "N-Term/Mgs Combo Not Found On N-Term Data Table"
- if it looks for B7 as ABcDE and finds ABCDE, it again replies "N-Term/Mgs Combo Not Found On N-Term Data Table"
- if it looks for B7 as ABcDE, and finds the all match with the small c, it does the rest of the calculation

This make sense?

 

[Peptide]

Thanks Eric,
So weird... I opened a new excel file entered it again like your example and I still get that it does not match. I did both CSE and non CSE options. I have the cells formatted as General - that make a difference?
Bob

 

[Peptide]

Hi Eric,
I have spent a bunch more time on this, I was able to duplicate on a new file but not on my real file.....oh so fun! Thx for your help. I ll keep trying!

 

[Eric W]

I'm glad you're making progress. Clearly there must be some difference between your sheet and mine, but it's very hard to say what without directly comparing files. Same thing with your new file vs. your real file. Must be some difference, but what? Cell formatting should not matter. Make sure the ranges are all correct, and that the $ signs are all in the right places. Let us know how you make out. Good luck!