Check cell value against an array and write corresponding result in new cell

[fewfish]

Hello


There is Col1 with text values. I have to check the value in Col1 and write corresponding value as result in Col2 depending on value in Col1. Value in Col2 are grouped in S and K group.


i.e.


Col1 Col2

a1 S

a2 S

a3 S

a4 K

a5 K


S <a1,a2,a3> K <a4,a5>


Thank you!

 

[fewfish]

I came up with this but it is too long for excel...


=if(or(G50="11-I";G50="12-I");"P1";IF(or(G50="9-I";G50="10-I";G50="16-III"G50="19-III");"S1";IF(or(G50="3-I";G50="4-I";G50="5-I";G50="6-I";G50="7-I";G50="8-I");"S2";IF(or(G50="2-I";G50="4-II";G50="4A-II";G50="4B-II");"M";IF(or(G50="1-I";G50="1-II";G50="2-II";G50="3-II");"P2";IF(or(G50="5-II";G50="6-II";G50="7-II";G50="8-II";G50="9-II";G50="10-II";G50="11-II";G50="12-II";G50="13-II";G50="14-II";G50="15-II";G50="16-II";G50="17-II";G50="18-II";G50="19-II";G50="4-III";G50="5-III";G50="6-III";G50="7-III";G50="8-III";G50="9-III";G50="10-III";G50="11-III";G50="12-III";G50="13-III";G50="14-III";G50="15-III";G50="17-III");"A";IF(or(G50="20-II";G50="20A-II");"K1";IF(or(G50="1-III";G50="2-III";G50="3-III");"S3";IF(or(G50="2-IV";G50="3-IV");"K2";IF(or(G50="21-II";G50="23-II";G50="24-II";G50="25-II";G50="18-III";G50="1-IV";G50="4-IV";G50="1-V";G50="2-V";G50="3-V";G50="22-II");"E";"nula"))))))))))


Please help

 

[Hui]

Fewfish


I have assumed that A1..A5 are in Col A

In Col B put either

=CHOOSE(RIGHT(A5,1),"S","S","S","K","K")

or

=IF(VALUE(RIGHT(A8,1))<4,"S","K")


I don't understand your second post


Or have i totally misread your first post ?

 

[fewfish]

Firs post is simplified case of my problem.


Second posts describe solution for the following:


P1 S1 S2 M P2 A K1 S3 K2 E

11-I 9-I 3-I 2-I 1-I 5-II 20-II 1-III 2-IV 21-II

12-I 10-I 4-I 4-II 1-II 6-II 20a-II 2-III 3-IV 22-II

16-III 5-I 4a-II 2-II 7-II 3-III 23-II

19-III 6-I 4b-II 3-II 8-II 24-II

7-I 9-II 25-II

8-I 10-II 18-III

11-II 1-IV

12-II 4-IV

13-II 1-V

14-II 2-V

15-II 3-V

16-II

17-II

18-II

19-II

4-III

5-III

6-III

7-III

8-III

9-III

10-III

11-III

12-III

13-III

14-III

15-III

17-III


where P1, S1, etc. are Categories and 11-I, 12-I etc. are Data that belong to Categories.


I have a separate list with 11-I, 12-I etc. data and I want to have Category name beside each data.


I have about 1000 rows of data to process and write Category name beside.

 

[Hui]

Can you post the file somewhere ?

 

[fewfish]

Here's the file


http://ifile.it/a243089


Thanks!

 

[fewfish]

No luck Hui?


Thanks anyway.

 

[Hui]

Fewfish

Whoops I knew there was one I'd forgotten about


Try this beauty


Setup a Named Range

Table: H4:Q31


and in


C4: =INDEX(H3:Q3,1,MATCH(B4,INDEX(Table,MIN(IF(Table=B4,ROW(Table)-MIN(ROW(Table))+1)),0),0))


Has to be array entered

Copy and paste into C4 Then press Ctrl Shift Enter, Not Enter by itself

Copy down

 

[fewfish]

Hej! It works for the first row...


I have to go home now and will try to adapt it to work for other rows.