Formula to recognize digits inside a series of numbers within the cell?

[brendaoc]

I would like to have a formula that recognizes a portion of the digits in Column A and produce a letter output if it meets the criteria. For example, if Col A has 358 in the number series then output is B, if Col A has 357 then output is C and so forth and so on. I have 17 output letters (A-Q). I have tons of data I need to apply this too. If have researched so many formulas. I am a novice when it comes to complex formulas so any help would be greatly appreciated. Thanks! Brenda

col A	Col B	Col C	Col d	Col E
916 - 35806 35806	AL7414	2499938	7/4/14	$7.26
3246 - 35758 35758	AL7414	2499966	7/4/14	$6.50
1038 - 36830 36830	AL7414	2500165	7/4/14	$0.82
586 - 35976 35976	AL7414	2500170	7/4/14	$3.15

[NBVC]

So if the 3 digits appear anywhere together in column A (or is it the always the first digits after the dash)?, then you want a letter "A", "B", "C", etc as the output?

Is it a downward consistent sequential referencing? ie. 358 = A, 357 = B, 356 = C, etc.. all the way to 342 = Q

[brendaoc]

Yes to your first question. It will be first digits after the dash.

A 356
B 258
C 257
D 355
E 359
F 354
G 352
G 351
G 350 (is this possible to put these 3 together for G?)
H 362
I 369
J 357
K 361
L 368
M 360
N 364
O 363
P 365
Q 366

[NBVC]

I am going to assume the first 3 digits after the dash....

First enter that last you just gave in a separater area (maybe in another tab) and you can use it to Lookup the items.

Now, use formula:

=INDEX(Sheet3!$A$1:$A$19,MATCH(--MID(A1,FIND("-",A1)+2,3),Sheet3!$B$1:$B$19,0))

where Sheet3, A1:B19 contains your list of letters vs numbers and A1 in the active sheet contains first code to purge. You can copy formula down.

If you get #N/A you have no match. If you want to hide the error with a blank or other text, then use IFERROR... e.g. =IFERROR(INDEX(Sheet3!$A$1:$A$19,MATCH(--MID(A1,FIND("-",A1)+2,3),Sheet3!$B$1:$B$19,0)),"")

[brendaoc]

worked fabulous on the first row of data, however it's not producing the correct output on the remaining rows. What to do? See attached.7414.xlsx7414.xlsx

[NBVC]

You put the same formula in those cells, instead of copying down. The copying down will change the A1 reference to A2, A3, etc...

select cell F1, then double-click the little black square at bottom right corner of the cell (or drag it down to the bottom).

Note: If there are no numbers after the dash you will get #VALUE error. The IFERROR() version I gave you will clean that up.

[bgoree09]

Good afternoon,

Drag or copy the formula down instead of using the same one.

Hope this helps.

[NBVC]

I think that is what I said.

[brendaoc]

thank you so much! I thought I had already done that without success, but I guess I was wrong! Thanks again!

[bgoree09]

It is