Find if value exists in multiple occurence

[inabanana]

I need help! I have two columns. Column A is a list of position ID and there are multiple rows for one value. Column B contains a corresponding org unit ID such that each position ID can have 3 to 5 org unit IDs linked to it. I have identified a list of org units that will be tagged as central function.

I need to check if a position has any org unit attached to it tagged as central function and return to me that org unit id in the third column

Column A Column B
12345 123
12345 234
23456 123
23456 234

123 is a central function so i need a formula that will get me the table below


Column A Column B Column C
12345 123 123
12345 234 123
23456 123 123
23456 234 123


I've tried using an array and max function by assigning a value to the central org unit but i'm stuck. Can anyone please help?

 

[Aladin Akyurek]

Does this work for you?

=IF(ISNUMBER(MATCH($B2,CentralFunctionList,0)),$B2,VLOOKUP($A2,$A$1:$C1,2,0))

 

[inabanana]

Does this work for you?

=IF(ISNUMBER(MATCH($B2,CentralFunctionList,0)),$B2,VLOOKUP($A2,$A$1:$C1,2,0))

Thank you for your response. However, I am getting an error as it is giving me only the value for the first record if the org unit is not in the Central Function List.

 

[Aladin Akyurek]

Thank you for your response. However, I am getting an error as it is giving me only the value for the first record if the org unit is not in the Central Function List.

Care to post that CentralFunctionList which you must have?