SOLVED

Need help with Excel

New Contributor

I need a help! I have a scenario where if i type a number then it should give me a corresponding coded value based on alpabet on another column. see below screenshot. 

Numbers from 1 to 0 are coded using word BLACKWHITE

Where B=1, L=2 etc. 

 

LionelCharley_0-1662195746297.png

 

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Lionel Charley Use below formula-

=CONCAT(XLOOKUP(--MID(N3,SEQUENCE(LEN(N3)),1),$C$3:$L$3,$C$2:$L$2,""))

For dynamic array approach try- 

=BYROW(N3:N7,LAMBDA(x,CONCAT(XLOOKUP(--MID(x,SEQUENCE(LEN(x)),1),C3:L3,C2:L2,""))))

 Harun24HR_0-1662198676589.png

 

You are awesome. @Harun24HR. Perfect!. Thank you for the help.

Glad to know. If it helps then please tick mark the answer to treat the thread as solved.