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

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!!!

CodePudding user response：

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.

CodePudding user response：

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.