Excel function

Copper Contributor

I need to write an excel function that: If column B color is #FFC7CE, then take column C value and copy it to D as many times Column B value is repetead on column A.


This is how it should look like:





10 Replies


In Excel functions don't work with cell properties like color, only with values. That's only with VBA if you consider such option.

Ok, thanks. Do you know if I can use another formula to check only for the repetead values?
I am trying something like this but having a SPILL error that doesn't let me know which are the obstructing cells.
=IF($D2=C:C,$E2, "")


Such formula returns entire column, i.e. 1 048 576 values. Thus it's not enough space to place the result, thus #SPILL error.

You may use something like

=IF( COUNTIF(C:C, D2) - 1, E2, "" )

@Sergei Baklan Thank you for your help, I've made progress but can't still reach to the desired result. Here is what I am getting:


D2 should be Value A also, because Value A is in cell B, so I have to copy that value to all D cells where A value is present in column A.

D3 value should be Value B.





Sorry, I didn't catch the logic.

"because Value A is in cell B" - in B1 you have "A", in B2 "C", there is no "Value A" in column B.


"I have to copy that value to all D cells where A value is present in column A"  - then

=IF(A1 = "A", "Value A", "have no idea what id shall be if not" )


"D3 value should be Value B" - why?

@Sergei Baklan 

Ok, here is the logic Sergei:
If column B1 value is wherever in column A, take C1 value and copy to D column A each row where value is B1.
So, in my example, if B1 is "A", then A is present two times in column A, so I have to pick value from C1 column ("Value A") and paste it in D column each time B1 value is in A column.
So, formula should return VALUE A for column D1, VALUE A for column D2 and VALUE C for column D3



Here is a better example so you don't get confused with Value A and Columns. 


Huge thanks!

best response confirmed by Mgaltieri (Copper Contributor)


Okay, thank you. Perhaps you mean


with formula in E1 as

=IFERROR( INDEX( C:C, MATCH( $A1, B:B, 0 ) ), $C1 )

and drag it down.

Awesome! Thank you very much Sergei for your help!

@Mgaltieri , you are welcome