If statement or Vlookup formula help

K

kumar1803

Board Regular
Joined
Jan 7, 2015
Messages
110
I have 500 rows of 6 digit numbers where I need to assigned a unique value based on first three digit of the number:

For example: 100000-199999 Assign value U023
200000-299999 Assign Value U220
300000-366999 Assign Value U222
367000-399999 Assign Value U367
670000-679999 Assign Value U 327

I can then just vlook up any value if they fall in the above table to get the value assign.

Here are the 6 digit value I have

100187
100188
130140
130143
130164
130184
130327
130385
130386
130451
140268
367115
367140
367149
367194
367325
367362
367366
367373
367374
100188
130142
130370
130373
130376
130386
130387
130654
130663
210137
210161
210185
367362
367631
100112
100113
100116
100120
100131
100132
100133
100135
100137
100138
100139
100140
100141
100145
100161
100162
100163
100167
100168
100170
100171
100180
100181
100182
100183
100185
100186
100187
100188
100189
100265
100631
110265
110613
130137
130140
130142
130143
130148
130151
130152
130166
130168
130170
130178
130180
130184
130187
130192
130315
130316
130325
130326
130327
130350
130352
130353
130360
130361
130362
130365
130370
130371
130373
130374
130375
130376
130377
130378
130379
130381
130383
130385
130386
130387
130388
130398
130410
130420
130421
130422
130423
130427
130441
130444
130445
130446
130451
130452
130453
130456
130617
130618
130619
130620
130625
130627
130628
130630
130633
130634
130635
130654
130663
130681
140268
160610
160611
160613
160614
160616
160619
210112
210113
210120
210127
210128
210129
210132
210133
210137
210138
210139
210140
210141
210143
210144
210149
210150
210161
210162
210163
210180
210181
210182
210183
210184
210185
210188
210189
300170
367110
367115
367128
367137
367138
367140
367141
367144
367146
367149
367157
367158
367159
367162
367172
367173
367174
367175
367176
367179
367186
367187
367188
367189
367191
367194
367325
367361
367362
367363
367366
367367
367368
367372
367373
367374
367451
367616
367631
367650
100182

<tbody>
</tbody><colgroup><col></colgroup>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Vlookup will work.


put the following data in cells D1:E6

100000 U023
200000 U220
300000 U222
367000 U367
670000 U327
680000 MAX


the this formual in B2 and Fill down
=VLOOKUP(A2,D:E,2)
 
Upvote 0
Hi,

I tried with the above formula, but all came with N/A as it did not exactly find the value as you can see the numbers above in my question.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,059
Messages
6,122,917
Members
449,093
Latest member
dbomb1414

Share this page

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