Jun 18 2020 09:33 AM
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 |
Jun 18 2020 09:46 AM
SolutionThat could be like
with formula
=LOOKUP(LEFT(A1,SEARCH(".",A1)-1),$D$1:$D$6,$E$1:$E$6)
Jun 18 2020 10:08 AM
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.
Jun 18 2020 11:12 AM
Jun 18 2020 11:17 AM
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.
Jun 19 2020 09:23 AM
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 |
Jun 19 2020 11:28 AM
Not sure I understood correctly, but if values into column Has from B, when like
=LEFT(B1,SEARCH(".",B1)-1)
Jun 23 2020 10:31 AM - edited Jun 23 2020 11:23 AM
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 |
|
|
|
Jun 23 2020 12:02 PM
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).
Jun 18 2020 09:46 AM
SolutionThat could be like
with formula
=LOOKUP(LEFT(A1,SEARCH(".",A1)-1),$D$1:$D$6,$E$1:$E$6)