VLOOKUP #REF! error

[MJ5479]

I have limited knowledge of excel so I apologize if the question is too simple...

I found a formula online that converts letters into numeric values and then sums them up...

=SUM(VLOOKUP(T(IF(1,MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))),SHEET2!$A$2:$B$29,2,FALSE))

So basically, the formula checks the contents of the cell C3 and if the field is filled with letters formula works great. If the field is empty, I get an #REF! error.

Is there any way to change this formula so that when C3 cell is empty, the return is a blank cell and not an error...

Thank you all in advance...
M.J.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MJ5479]

Thank you, I did it previously but it was probably changed because I did a password reset today...

That is also one of the things I didn't take into an account... This worksheet I'm making is going to be shared with broad range of people using different versions of Excel...

 

[Fluff]

Thanks for updating your profile
If it needs to work in earlier version then try

=IF(C3="","",SUM(VLOOKUP(T(IF(1,MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))),Sheet2!$A$2:$B$29,2,FALSE)))

 

[MJ5479]

Thank you, thank you, thank you.... I've just tested it and it works absolutely perfectly...

You saved me so much time...

And also again thank you for pointing out that I have to pay attention to other versions of Excel...

All the best....
M.J.

 

[Fluff]

You're welcome & thanks for the feedback.