SOLVED

# Excel function

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: Thanks!

10 Replies

# Re: Excel function

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

# Re: Excel function

Ok, thanks. Do you know if I can use another formula to check only for the repetead values?

# Re: Excel function

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, "")

# Re: Excel function

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, "" )``

# Re: Excel function

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

Thanks

# Re: Excel function

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?

# Re: Excel function

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

# Re: Excel function

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.

# Re: Excel function

Awesome! Thank you very much Sergei for your help!

# Re: Excel function

@Mgaltieri , you are welcome