Formulas and Functions

[Oliullah_Siddique]

Dear Everyone,

@Riny_van_Eekelen 

Would you please help me with the following formula,

	A	B	C
	Particulars	Criteria	Result
1	101010100000	If 1st digit of Cell A1 is 1 and 3rd digit of Cell A1 is 1, Cell C1 will show CA	CA
2	102020100000	If 1st digit of Cell A2 is 1 and 3rd digit of Cell A2 is 2, Cell C2 will show NCA	NCA
3	103010000000	If 1st digit of Cell A3 is 1 and 3rd digit of Cell A3 is 3, Cell C3 will show IA	IA
4	104010300000	If 1st digit of Cell A4 is 1 and 3rd digit of Cell A4 is 4, Cell C4 will show INV	INV
5	201010100000	If 1st digit of Cell A5 is 2 and 3rd digit of Cell A5 is 1, Cell C5 will show SE	SE
6	202030300000	If 1st digit of Cell A6 is 2 and 3rd digit of Cell A6 is 2, Cell C6 will show NCL	NCL
7	203030300000	If 1st digit of Cell A7 is 2 and 3rd digit of Cell A7 is 3, Cell C7 will show CL	CL
8	301010100000	If 1st digit of Cell A8 is 3 and 3rd digit of Cell A8 is 1, Cell C8 will show OE	OE
9	302020000000	If 1st digit of Cell A9 is 3 and 3rd digit of Cell A9 is 2, Cell C9 will show Other	Other
10	401010200000	If 1st digit of Cell A10 is 4 and 3rd digit of Cell A10 is 1, Cell C10 will show Exp	Exp
11	402010200003	If 1st digit of Cell A11 is 4 and 3rd digit of Cell A11 is 2, Cell C11 will show FC	FC

Thanks in advance.

[#2]

@Oliullah_Siddique 

Here is an approach for A1 because you could switch to the other lines.
Maybe it helps :)

=IF(MID(A1,1,1)="1",IF(MID(A1,3,1)="1","CA",""))

...if it's not what you're looking for, please just ignore it :)

[#3 · Accepted solution]

@Oliullah_Siddique You can do that in several ways. I've included just two of them in the attached workbook. It all depend a bit on how much data yo have, how often you need to change codes etc.

In general, I prefer the solution with a lookup table. The formula get easier to write and read and it easier to maintain.

[#4]

Thanks @Riny_van_Eekelen

Exactly what I was looking for.

[#5]

Thank you very much @NikolinoDE