Excel VLOOKUP to extract alphabet postion in name

Question

ANTHONY in this name i want 4th character alphabet position EX: ANT(8) i have to get like this

i tried this formula =VLOOKUP(MID(A2,4,1),$J:$K,2,0) i can able to get just position only like 8 but i want result like this ANT(8)

Answer 1

Concatenate:

=LEFT(A2,3)&"("&VLOOKUP(MID(A2,4,1),$J:$K,2,0)&")"

But you do not need the VLOOKUP we can just use the CODE and some math:

=LEFT(A2,3)&"("&CODE(UPPER(MID(A2,4,1)))-64&")"

Now it is self contained without the need of a lookup.