If Formula

kumar1803

Board Regular
Joined
Jan 7, 2015
Messages
110
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
@ 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"
 
Upvote 0
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
AB
1367650U367
2110613U020
3210151U220
4210113U220
5210151U220
6130654U020
7367110U367
8210151U220
9130325U020
10210151U220
11311626Not Listed
12210151U220
13160610U020
14210151U220
15210151U220
16210151U220
17210151U220
18210151U220
19210151U220
20210151U220
21367325U367
22210151U220
23130362U020
24210151U220
25677123U327
Sheet3
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,915
Messages
6,181,725
Members
453,064
Latest member
robatthe2A

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top