Forum Discussion

Copper Contributor

Jul 11, 2023

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

excel

Formulas and Functions

SergeiBaklan
Jul 11, 2023
Mgaltieri

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.
fill names.xlsx10 KB

SergeiBaklan

Diamond Contributor

Jul 11, 2023

Mgaltieri

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

Mgaltieri

Copper Contributor

Jul 11, 2023

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

SergeiBaklan
Diamond Contributor
Jul 11, 2023
Mgaltieri

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, "" )
- Mgaltieri
  Copper Contributor
  Jul 11, 2023
  SergeiBaklan 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
  - SergeiBaklan
    Diamond Contributor
    Jul 11, 2023
    Mgaltieri
    
    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?