SOLVED

Excel Formula: Look up column A, and if in Reference List, then fill column B accordingly

Highlighted
Occasional Contributor

Hello,

 

I need an Excel formula that look for roman number (see Reference List below), and if in this list, then fill column B. Can you help?

 

Thanks.

 

REFERENCE LIST

I = LAISSE

II = STROPHIQUES

III = EN FORME DE DIALOGUE

IV = ÉNUMÉRATIVES

V = BRÈVES

VI = TIMBRES

 

A

B

II.C-36.1

STROPHIQUES

II.H-35.1

STROPHIQUES

II.C-24.1

STROPHIQUES

II.E-33.1

STROPHIQUES

II.C-9.1

STROPHIQUES

II.H-2.4

STROPHIQUES

I.O-3.6

LAISSE

II.P-26.2

STROPHIQUES

III.H-15.1A

EN FORME DE DIALOGUE

8 Replies
Highlighted
Best Response confirmed by WorrypasCanada (Occasional Contributor)
Solution

@WorrypasCanada 

That could be like

image.png

with formula

=LOOKUP(LEFT(A1,SEARCH(".",A1)-1),$D$1:$D$6,$E$1:$E$6)
Highlighted

Marvellous, it works, thanks.

 

Another particularity in my column A, I've got some cells that yet to be determined.

 

A

B

II.C-36.1

STROPHIQUES

II.H-35.1

STROPHIQUES

II.C-24.1

STROPHIQUES

II.E-33.1

STROPHIQUES

II.C-9.1

STROPHIQUES

II.H-2.4

STROPHIQUES

UNDETERMINED

UNDETERMINED

II.P-26.2

STROPHIQUES

III.H-15.1A

EN FORME DE DIALOGUE

 

I apologize, I should had mentioned. How can I modify?

 

Robert.

Highlighted
Highlighted

@WorrypasCanada 

You may wrap formula with IFERROR

=IFERROR(LOOKUP(LEFT(A10,SEARCH(".",A10)-1),$D$1:$D$6,$E$1:$E$6),"UNDETERMINED")

Please check in attached file.

Highlighted

Hello,

 

I wish to reverse the formula, to generate column A Roman Numbers.

 

How you can help?

 

Thanks.

 

Robert.

 

REFERENCE LIST

I = LAISSE

II = STROPHIQUES

III = EN FORME DE DIALOGUE

IV = ÉNUMÉRATIVES

V = BRÈVES

VI = TIMBRES

 

A

B

C

II

II.C-36.1

STROPHIQUES

II

II.H-35.1

STROPHIQUES

II

II.C-24.1

STROPHIQUES

II

II.E-33.1

STROPHIQUES

II

II.C-9.1

STROPHIQUES

II

II.H-2.4

STROPHIQUES

UNDETERMINED

UNDETERMINED

UNDETERMINED

II

II.P-26.2

STROPHIQUES

III

III.H-15.1A

EN FORME DE DIALOGUE

 

Highlighted

@WorrypasCanada 

Not sure I understood correctly, but if values into column Has from B, when like

=LEFT(B1,SEARCH(".",B1)-1)

 

Highlighted

@Sergei Baklan

 

Hello,

 

I wish to apply an Excel formula in Column B, that looks in Column A, and refers to Column D and E (Reference List), then AutoFills with Roman numerals as shown. Can you help?

 

Again, I’ve got some cells in Column A that are Undetermined. I've modify one your suggested formulas, but it doesn't work.

 

Thanks for your patience,

 

Robert Richard

 

A

B

C

D

E

STROPHIQUES

II

 

I

LAISSE

STROPHIQUES

II

 

II

STROPHIQUES

STROPHIQUES

II

 

III

EN FORME DE DIALOGUE

STROPHIQUES

II

 

IV

ÉNUMÉRATIVES

STROPHIQUES

II

 

V

BRÈVES

STROPHIQUES

II

 

VI

TIMBRES

UNDETERMINED

UNDETERMINED

 

 

 

STROPHIQUES

II

 

 

 

EN FORME DE DIALOGUE

III

 

 

 

 

 

 

Highlighted

@WorrypasCanada 

Robert, formula in column B could be

=IFNA(INDEX([LÉGENDES POUR FORMULES EXCEL],MATCH([@[CÔTES AU CATALOGUE]],[LÉGENDES POUR FORMULES EXCEL2],0)),"UNDETERMINED")

If you open attached file it will be converted into your locale automatically (if it's not English).