SOLVED

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

Occasional Contributor

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

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
Best Response confirmed by WorrypasCanada (Occasional Contributor)
Solution

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

That could be like

with formula

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

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

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.

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

With UNDETERMINED, I get this: #VALUE!

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

You may wrap formula with IFERROR

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

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

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

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

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

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

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

@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.

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

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

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).