SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1474688%22%20slang%3D%22fr-FR%22%3EExcel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474688%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20an%20Excel%20formula%20that%20look%20for%20roman%20number%20(see%20Reference%20List%20below)%2C%20and%20if%20in%20this%20list%2C%20then%20fill%20column%20B.%20Can%20you%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EREFERENCE%20LIST%3C%2FP%3E%3CP%3EI%20-%20LEASH%3C%2FP%3E%3CP%3EII%20-%20STROPHICS%3C%2FP%3E%3CP%3EIII%20-%20IN%20THE%20FORM%20OF%20DIALOGUE%3C%2FP%3E%3CP%3EIV%20-%20ENUMERATIVES%3C%2FP%3E%3CP%3EV%20-%20BRIEF%3C%2FP%3E%3CP%3EVI%20-%20STAMPS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3EHas%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A1.%20C-36.1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A1.%20H-35.1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A1.%20C-24.1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A1.%20E-33.1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A1.%20C-9.1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A1.%20H-2.4%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EI.O-3.6%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ELeaves%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A1.%20P-26.2%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%E2%85%A2.%20H-15.1A%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EIN%20THE%20FORM%20OF%20DIALOGUE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1474688%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474826%22%20slang%3D%22fr-FR%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474826%22%20slang%3D%22fr-FR%22%3E%3CP%3EMarvellous%2C%20it%20works%2C%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20particularity%20in%20my%20column%20A%2C%20I've%20got%20some%20cells%20that%20yet%20to%20be%20determined.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2284px%22%3E%3CP%20class%3D%22p1%22%3E%3CSTRONG%3EHas%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2284px%22%3E%3CP%20class%3D%22p1%22%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20C-36.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20H-35.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20C-24.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20E-33.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20C-9.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20H-2.4%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3EUNDETERMINED%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3EUNDETERMINED%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20P-26.2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22214.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3E%E2%85%A2.%20H-15.1A%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22287.296875px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3EIN%20THE%20FORM%20OF%20DIALOGUE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20apologize%2C%20I%20should%20have%20mentioned.%20How%20can%20I%20modify%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERobert.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474752%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474752%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703787%22%20target%3D%22_blank%22%3E%40WorrypasCanada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20568px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199535iF2BED476B0779E7A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(LEFT(A1%2CSEARCH(%22.%22%2CA1)-1)%2C%24D%241%3A%24D%246%2C%24E%241%3A%24E%246)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1475025%22%20slang%3D%22fr-FR%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1475025%22%20slang%3D%22fr-FR%22%3EWith%20UNDETERMINED%2C%20I%20get%20this%3A%20%23VALUE!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1475037%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1475037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703787%22%20target%3D%22_blank%22%3E%40WorrypasCanada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20wrap%20formula%20with%20IFERROR%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(LOOKUP(LEFT(A10%2CSEARCH(%22.%22%2CA10)-1)%2C%24D%241%3A%24D%246%2C%24E%241%3A%24E%246)%2C%22UNDETERMINED%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1477449%22%20slang%3D%22fr-FR%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1477449%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20to%20reverse%20the%20formula%2C%20to%20generate%20column%20A%20Roman%20Numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20you%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERobert.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EREFERENCE%20LIST%3C%2FP%3E%3CP%3EI%20-%20LEASH%3C%2FP%3E%3CP%3EII%20-%20STROPHICS%3C%2FP%3E%3CP%3EIII%20-%20IN%20THE%20FORM%20OF%20DIALOGUE%3C%2FP%3E%3CP%3EIV%20-%20ENUMERATIVES%3C%2FP%3E%3CP%3EV%20-%20BRIEF%3C%2FP%3E%3CP%3EVI%20-%20STAMPS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22523px%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2284px%22%3E%3CP%20class%3D%22p1%22%3E%3CSTRONG%3EHas%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2284px%22%3E%3CP%20class%3D%22p1%22%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2284px%22%3E%3CP%20class%3D%22p1%22%3E%3CSTRONG%3EC%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20C-36.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20H-35.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20C-24.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20E-33.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20C-9.1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20H-2.4%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3EUNDETERMINED%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3EUNDETERMINED%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3EUNDETERMINED%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p3%22%3E%E2%85%A1.%20P-26.2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22106.625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3E%E2%85%A2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22129.265625px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3E%E2%85%A2.%20H-15.1A%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22286.125px%22%20height%3D%2230px%22%3E%3CP%20class%3D%22p4%22%3EIN%20THE%20FORM%20OF%20DIALOGUE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1477687%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1477687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703787%22%20target%3D%22_blank%22%3E%40WorrypasCanada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20I%20understood%20correctly%2C%20but%20if%20values%20into%20column%20%3CSTRONG%3EHas%3C%2FSTRONG%3E%20from%20%3CSTRONG%3EB%3C%2FSTRONG%3E%2C%20when%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLEFT(B1%2CSEARCH(%22.%22%2CB1)-1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1484288%22%20slang%3D%22fr-FR%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1484288%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20to%20apply%20an%20Excel%20formula%20in%20Column%20B%2C%20that%20looks%20in%20Column%20A%2C%20and%20refers%20to%20Column%20D%20and%20E%20(Reference%20List)%2C%20then%20AutoFills%20with%20Roman%20numerals%20as%20shown.%20Can%20you%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20I've%20got%20some%20cells%20in%20Column%20A%20that%20are%20Undetermined.%20I've%20modify%20one%20your%20suggested%20formulas%2C%20but%20it%20doesn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20patience%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERobert%20Richard%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3EHas%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EC%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3ED%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EE%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EI%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ELeaves%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A2%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EIN%20THE%20FORM%20OF%20DIALOGUE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EIv%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EENUMERATIVES%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EV%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EBrief%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EVi%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EStamps%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EUNDETERMINED%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EUNDETERMINED%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ESTROPHICS%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EIN%20THE%20FORM%20OF%20DIALOGUE%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%E2%85%A2%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1484467%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3A%20Look%20up%20column%20A%2C%20and%20if%20in%20Reference%20List%2C%20then%20fill%20column%20B%20accordingly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1484467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703787%22%20target%3D%22_blank%22%3E%40WorrypasCanada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERobert%2C%20formula%20in%20column%20B%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(INDEX(%5BL%C3%89GENDES%20POUR%20FORMULES%20EXCEL%5D%2CMATCH(%5B%40%5BC%C3%94TES%20AU%20CATALOGUE%5D%5D%2C%5BL%C3%89GENDES%20POUR%20FORMULES%20EXCEL2%5D%2C0))%2C%22UNDETERMINED%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20you%20open%20attached%20file%20it%20will%20be%20converted%20into%20your%20locale%20automatically%20(if%20it's%20not%20English).%3C%2FP%3E%3C%2FLINGO-BODY%3E
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
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)

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.

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

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

 

@WorrypasCanada 

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

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

 

@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

 

 

 

 

 

 

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