If Formula

[kumar1803]

Hello,

I need help with IF formula or vlookup for the following situation

Column A

100-160
200-210
300-310
367-367
67-67

If my first three digit falls in the above number , I would ike to assign the following values

100-160 value "U020"
200-210 Value "U220"
300-310 Value " U222"
367-367 Value " U367"
67-67 Value "U327"

Thank you in advance for your help.

 

[Johnny Thunder]

Here you go

Formula: =IF(A9>=100,"U020",IF(A9>=200,"U220",IF(A9>=300,"U222",IF(A9>=367,"U367",IF(A9=67,"U327","NotFound ")))))

Update A9 to suit your reference.

Hello,

I need help with IF formula or vlookup for the following situation

Column A

100-160
200-210
300-310
367-367
67-67

If my first three digit falls in the above number , I would ike to assign the following values

100-160 value "U020"
200-210 Value "U220"
300-310 Value " U222"
367-367 Value " U367"
67-67 Value "U327"

Thank you in advance for your help.

 

[kumar1803]

Thank you for a quick reply,

Actually my A9 cell has 6 digits numbers. I like to look up only the first three digits in the above range and assign value accordingly. Can you please restructure your formula to fit this requirements.

Sorry for not indicating it earlier.

 

[jtakw]

Here you go

Formula: =IF(A9>=100,"U020",IF(A9>=200,"U220",IF(A9>=300,"U222",IF(A9>=367,"U367",IF(A9=67,"U327","NotFound ")))))

Update A9 to suit your reference.

Actually, this formula will ALWAYS return U020 for anything 100 or higher.

 

[jtakw]

Thank you for a quick reply,

Actually my A9 cell has 6 digits numbers. I like to look up only the first three digits in the above range and assign value accordingly. Can you please restructure your formula to fit this requirements.

Sorry for not indicating it earlier.

Can you show some more samples? With expected results.
Can you separate the 100-160 to two columns?
When you say 6 digits, do you mean 6 digits with a dash? (100-160)
When you say you ONLY want to look up the first 3 digits, what do you mean?

 

[kumar1803]

Yes, agree with jtakw


Its bringing the value to "u020" for 367650. It should have been U367 .

 

[kumar1803]

@ JTAKW:

Here is the column A number:

[TABLE="width: 147"]
<tbody>[TR]
[TD]367650[/TD]
[/TR]
[TR]
[TD]110613[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]210113[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]130654[/TD]
[/TR]
[TR]
[TD]367110[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]130325[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]311626[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]160610[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]367325[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
[TR]
[TD]130362[/TD]
[/TR]
[TR]
[TD]210151[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

Based on first three digits above, i would like the following to be assigned:

If first three digits falls between 100-160 then assign "U023"
200-210 Value "U220"
300-310 Value " U222"
367-367 Value " U367"
67-67 Value "U327"

 

[jtakw]

Hi,

Without the use of a helper column to isolate the first 3 digits (in the case of 67, 2 digits), the formula gets a little messy:

Book1
	A	B
1	367650	U367
2	110613	U020
3	210151	U220
4	210113	U220
5	210151	U220
6	130654	U020
7	367110	U367
8	210151	U220
9	130325	U020
10	210151	U220
11	311626	Not Listed
12	210151	U220
13	160610	U020
14	210151	U220
15	210151	U220
16	210151	U220
17	210151	U220
18	210151	U220
19	210151	U220
20	210151	U220
21	367325	U367
22	210151	U220
23	130362	U020
24	210151	U220
25	677123	U327
Sheet3

Cell Formulas
Range	Formula
B1	=IF(LEFT(A1,2)="67","U327",IF(LEFT(A1,3)="367","U367",IF(AND(LEFT(A1,3)>="100",LEFT(A1,3)<="160"),"U020",IF(AND(LEFT(A1,3)>="200",LEFT(A1,3)<="210"),"U220",IF(AND(LEFT(A1,3)>="300",LEFT(A1,3)<="310"),"U222","Not Listed")))))

Formula copied down.