Fetch Alphabets in front of codes.

[parankush]

I want the Alphabets in front of the Product Code according to the range they belong. Tried Vlookup and IF formula but it wont help.

Book2
	A	B	C	D	E
1	Product Code	Alphabets		Range	Alphabets
2	10000			10000-10999	A
3	10001			11000-11999	B
4	10002			12000-12999	C
5	10003			13000-13999	D
6	10004
7	10005
8	10006
9	10007
10	10008
11	10009
12	10010
13	10011
14	10012
15	10013
16	11000
17	11001
18	11002
19	11003
20	11004
21	11005
22	12000
23	12202
24	13203
25	14000
26	12503
27	13504
28	12504
29	10005
Sheet1

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	Product Code	Alphabets		Range	Alphabets
2	9999			10000	A
3	10001	A		11000	B
4	10002	A		12000	C
5	10003	A		13000	D
6	10004	A		14000
7	10005	A
8	10006	A
9	10007	A
10	10008	A
11	10009	A
12	10010	A
13	10011	A
14	10012	A
15	10013	A
16	11000	B
17	11001	B
18	11002	B
19	11003	B
20	11004	B
21	11005	B
22	12000	C
23	12202	C
24	13203	D
25	14000
26	12503	C
27	13504	D
28	12504	C
29	10005	A
Result

Cell Formulas
Range	Formula
B2:B29	B2	=IFERROR(T(INDEX($E$2:$E$6,MATCH(A2,$D$2:$D$6,1))),"")

 

[Rick Rothstein]

Here is another formula that you can try...

=LOOKUP(A2/1000,{0,10,11,12,13,14},{"","A","B","C","D",""})

 

[Rick Rothstein]

Here is another formula that you can try...

=LOOKUP(A2/1000,{0,10,11,12,13,14},{"","A","B","C","D",""})

And here is one more...

=MID("ABCD",1+MOD(MAX(9999,A2)/1000,10),1)

 

[parankush]

Thank You everyone for all the replies.
All the formulas are working and will help me in getting the required result.

 

[Fluff]

Glad we could help & thanks for the feedback.