Forum Discussion

Copper Contributor

Nov 23, 2023

Solved

Replicate multiple if statements through a column

Hi all, Kind of new to all this in excel. I have a, multiple if statement in D3. D3 Formula: =IF(AND(J3<(D3/2),(D3/2)<K3),L3*2,IF(AND(J4<(D3/2),(D3/2)<K4),L4*2,IF(AND(J5<(D3/2),...

excel

Formulas and Functions

OliverScheurich
Nov 23, 2023
notanitgod
In cell D3 the formula returns a circular reference but you can enter the formula for example in cell C3:

=IF(AND($J$3<(D3/2),(D3/2)<$K$3),$L$3*2,
IF(AND($J$4<(D3/2),(D3/2)<$K$4),$L$4*2,
IF(AND($J$5<(D3/2),(D3/2)<$K$5),$L$5*2,
IF(AND($J$6<(D3/2),(D3/2)<$K$6),$L$6*2,
IF(AND($J$7<(D3/2),(D3/2)<$K$7),$L$7*2,
IF(AND($J$8<(D3/2),(D3/2)<$K$8),$L$8*2,
IF(AND($J$9<(D3/2),(D3/2)<$K$9),$L$9*2,
IF(AND($J$10<(D3/2),(D3/2)<$K$10),$L$10*2,
IF(AND($J$11<(D3/2),(D3/2)<$K$11),$L$11*2,
IF(AND($J$12<(D3/2),(D3/2)<$K$12),$L$12*2,
IF(AND($J$13<(D3/2),(D3/2)<$K$13),$L$13*2,
IF(AND($J$14<(D3/2),(D3/2)<$K$14),$L$14*2,
IF(AND($J$15<(D3/2),(D3/2)<$K$15),$L$15*2,
IF(AND($J$16<(D3/2),(D3/2)<$K$16),$L$16*2,
IF(AND($J$17<(D3/2),(D3/2)<$K$17),$L$17*2,
IF(AND($J$18<(D3/2),(D3/2)<$K$18),$L$18*2,0))))))))))))))))

When you fill the formula down this is returned in cell C9:

=IF(AND($J$3<(D9/2),(D9/2)<$K$3),$L$3*2,
IF(AND($J$4<(D9/2),(D9/2)<$K$4),$L$4*2,
IF(AND($J$5<(D9/2),(D9/2)<$K$5),$L$5*2,
IF(AND($J$6<(D9/2),(D9/2)<$K$6),$L$6*2,
IF(AND($J$7<(D9/2),(D9/2)<$K$7),$L$7*2,
IF(AND($J$8<(D9/2),(D9/2)<$K$8),$L$8*2,
IF(AND($J$9<(D9/2),(D9/2)<$K$9),$L$9*2,
IF(AND($J$10<(D9/2),(D9/2)<$K$10),$L$10*2,
IF(AND($J$11<(D9/2),(D9/2)<$K$11),$L$11*2,
IF(AND($J$12<(D9/2),(D9/2)<$K$12),$L$12*2,
IF(AND($J$13<(D9/2),(D9/2)<$K$13),$L$13*2,
IF(AND($J$14<(D9/2),(D9/2)<$K$14),$L$14*2,
IF(AND($J$15<(D9/2),(D9/2)<$K$15),$L$15*2,
IF(AND($J$16<(D9/2),(D9/2)<$K$16),$L$16*2,
IF(AND($J$17<(D9/2),(D9/2)<$K$17),$L$17*2,
IF(AND($J$18<(D9/2),(D9/2)<$K$18),$L$18*2,0))))))))))))))))

OliverScheurich

Gold Contributor

Nov 23, 2023

notanitgod

In cell D3 the formula returns a circular reference but you can enter the formula for example in cell C3:

=IF(AND($J$3<(D3/2),(D3/2)<$K$3),$L$3*2,

IF(AND($J$4<(D3/2),(D3/2)<$K$4),$L$4*2,

IF(AND($J$5<(D3/2),(D3/2)<$K$5),$L$5*2,

IF(AND($J$6<(D3/2),(D3/2)<$K$6),$L$6*2,

IF(AND($J$7<(D3/2),(D3/2)<$K$7),$L$7*2,

IF(AND($J$8<(D3/2),(D3/2)<$K$8),$L$8*2,

IF(AND($J$9<(D3/2),(D3/2)<$K$9),$L$9*2,

IF(AND($J$10<(D3/2),(D3/2)<$K$10),$L$10*2,

IF(AND($J$11<(D3/2),(D3/2)<$K$11),$L$11*2,

IF(AND($J$12<(D3/2),(D3/2)<$K$12),$L$12*2,

IF(AND($J$13<(D3/2),(D3/2)<$K$13),$L$13*2,

IF(AND($J$14<(D3/2),(D3/2)<$K$14),$L$14*2,

IF(AND($J$15<(D3/2),(D3/2)<$K$15),$L$15*2,

IF(AND($J$16<(D3/2),(D3/2)<$K$16),$L$16*2,

IF(AND($J$17<(D3/2),(D3/2)<$K$17),$L$17*2,

IF(AND($J$18<(D3/2),(D3/2)<$K$18),$L$18*2,0))))))))))))))))

When you fill the formula down this is returned in cell C9:

=IF(AND($J$3<(D9/2),(D9/2)<$K$3),$L$3*2,

IF(AND($J$4<(D9/2),(D9/2)<$K$4),$L$4*2,

IF(AND($J$5<(D9/2),(D9/2)<$K$5),$L$5*2,

IF(AND($J$6<(D9/2),(D9/2)<$K$6),$L$6*2,

IF(AND($J$7<(D9/2),(D9/2)<$K$7),$L$7*2,

IF(AND($J$8<(D9/2),(D9/2)<$K$8),$L$8*2,

IF(AND($J$9<(D9/2),(D9/2)<$K$9),$L$9*2,

IF(AND($J$10<(D9/2),(D9/2)<$K$10),$L$10*2,

IF(AND($J$11<(D9/2),(D9/2)<$K$11),$L$11*2,

IF(AND($J$12<(D9/2),(D9/2)<$K$12),$L$12*2,

IF(AND($J$13<(D9/2),(D9/2)<$K$13),$L$13*2,

IF(AND($J$14<(D9/2),(D9/2)<$K$14),$L$14*2,

IF(AND($J$15<(D9/2),(D9/2)<$K$15),$L$15*2,

IF(AND($J$16<(D9/2),(D9/2)<$K$16),$L$16*2,

IF(AND($J$17<(D9/2),(D9/2)<$K$17),$L$17*2,

IF(AND($J$18<(D9/2),(D9/2)<$K$18),$L$18*2,0))))))))))))))))

notanitgod

Copper Contributor

Nov 23, 2023

Bro, thank you so much. so I just need to put those $, where you did to get this to work? Dude, I've been trying to get this working for almost 3 hours now, resorted to doing it manual, until you came along.

Can't express how much I appreciate you help. Have an amazing day dude.

peiyezhu
Bronze Contributor
Nov 24, 2023
please share some data and expected result.
- notanitgod
  Copper Contributor
  Nov 24, 2023
  Hi, the solution by OliverScheurich solved my problem exactly.
  - djclements
    Silver Contributor
    Nov 25, 2023
    notanitgod Glad to hear your cell reference problem was solved. As an aside, the use of multiple nested IF formulas can be simplified here with a single lookup function, such as the INDEX/MATCH method, or the XLOOKUP function (if it's available in your version of Excel). For example:
    
    =IFERROR(INDEX($L$3:$L$18, MATCH(1, ($J$3:$J$18<(D3/2))*((D3/2)<$K$3:$K$18), 0))*2, 0)
    
    - OR -
    
    =XLOOKUP(1, ($J$3:$J$18<(D3/2))*((D3/2)<$K$3:$K$18), $L$3:$L$18, 0)*2
    
    Note: the XLOOKUP function is only available for use in Excel 2021 or later. If you have an earlier version of Excel, the INDEX/MATCH method can be used, but may require pressing Ctrl+Shift+Enter on your keyboard to enter the formula in the cell.