Is there a way to convert this formula so it is case sensitive and works with numbers too

Question

I use the following formula to sum the values corresponding to the characters.

{=SUM(VLOOKUP(T(IF(1,MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),values,2,0))} . It worked but can't get it to work case-sensitive and with numbers.

If a=1.325 b=1.5 A=1.5 2=1.5 ->

ab = a+b = 1.325+1.5 = 2.825

ab = 2.825 (works)
Ab = 3 (doesn't work)
ab2 = 4.325 (doesn't work)
Ab2 = 4.5 (doesn't work)

Maybe using TRUE index match function, couldn't figure it out.

Any help greatly appreciated. Thank you!!!

Have you tried 'EXACT'? e.g. {=EXACT(SUM(VLOOKUP(T(IF(1,MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),values,2,0)))} — GoodJuJu, Sep 01 '22 at 06:07

score 2 · Accepted Answer · answered Sep 01 '22 at 06:15

2

From the formula you post, I assume you're using an old version of Excel, so you should use something like:

=SUM(INDEX(values,,2)*MMULT(N(EXACT(INDEX(values,,1),MID(A2,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A2)))),1))),ROW(INDIRECT("1:"&LEN(A2)))^0))

which will most likely require committing with CTRL+SHIFT+ENTER for your version of Excel.

answered Sep 01 '22 at 06:15

Jos Woolley

Thank you!!. I am actually using 365, just old school I guess. What would be the modern/365 way to do this? – andnand Sep 02 '22 at 00:23
what would be the modern/365 way to do this? – andnand Sep 02 '22 at 15:28
For O365: `=SUM(INDEX(values,,2)*MMULT(N(EXACT(INDEX(values,,1),MID(A2,SEQUENCE(,LEN(A2)),1))),SEQUENCE(LEN(A2),,,0)))` – Jos Woolley Sep 03 '22 at 06:56

JvdV · Answer 2 · 2022-09-01T06:47:01.087

VLOOKUP() is not equipped to do case-sensitive matching. Very few functions actually are. One is FIND(). You could try:

Formula in B1:

=SUM(IFERROR(FIND(TRANSPOSE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)),D$1:D$4),0)*E$1:E$4)

Or, in terms of your named range:

=SUM(IFERROR(FIND(TRANSPOSE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)),INDEX(values,,1)),0)*INDEX(values,,2))

Just like @JosWoolley I assumed an Excel version prior to ms365. Therefor confirm through Ctrl+Shift+Enter.

2 Answers2