Macro to lookup reference and exclude secondary value

  • I am trying to find if there is a Macro that I can write to match multiple reference on one sheet and find a value from second sheet excluding the duplicate value.

    For Example, I am looking to match reference 1 & A on sheet 1 to sheet 2. The value for reference A on sheet 2 has multiple values (A & B), but the value I need is B not A. I am trying to find, if there is a macro I could write that will give me the value B and not A.

    I am using Index function, but it is giving me value B for some result. However, for other value it is giving me value A as B is primary and A secondary on that. Please help write a Macro that will give me only unique value that matches with A.

    1A 1 A
    2B B
    3C 2 B