Archie Way
New Member
Hello,
Please can you help me combine these formulas with an IF statement. I am looking for 4 new formulas (1 per category).
Row 1 qty:
I want it to say if column G equals 'FamilyA' then do this calculation =IFERROR(INDEX(sheet1!$D$2:$D$10000,MATCH(TRUE,EXACT(C2,sheet!$C$2:$C$10000),0)),0)
Or if it equals 'FamilyB' do this calculation
=IFERROR(INDEX(sheet2!$F$2:$F$10000,MATCH(TRUE,EXACT(C2,sheet2!$A$2:$A$10000),0)),0)
Row 1 date:
If column G equals FamilyA do this
=IFERROR(INDEX(sheet1!$D$2:$D$10000,MATCH(TRUE,EXACT(C2,sheet1!$C$2:$C$10000),0)),0)
Or if column G equals FamilyB do this
=IFERROR(INDEX(sheet2!$E:$E,MATCH(TRUE,EXACT(C2,sheet2!$A:$A),0)),0)
Row 2 qty:
If column G equals FamilyA do this
=IFERROR(INDEX(sheet1$D$2:$D$10000,SMALL(IF(C2=sheet1!$C$2:$C$10000,ROW(sheet1!$C$2:$C$10000)-ROW(sheet1!$C$2)+1),2)),0)
Or if column G equals FamilyB do this
=IFERROR(INDEX(sheet2!$F$2:$F$10000,SMALL(IF(C2=sheet2!$A$2:$A$10000,ROW(sheet2!$A$2:$A$10000)-ROW(sheet2!$A$2)+1),2)),0)
Row 2 date:
If column G equals FamilyA do this
=IFERROR(INDEX(sheet1!$G$2:$G$10000,SMALL(IF(C2=sheet1!$C$2:$C$10000,ROW(sheet1!$C$2:$C$10000)-ROW(sheet1!$C$2)+1),2)),0)
Or if column G equals FamilyB do this
=IFERROR(INDEX(sheet2!$E$2:$E$10000,SMALL(IF(C2=sheet2!$A$2:$A$10000,ROW(sheet2!$A$2:$A$10000)-ROW(sheet2!$A$2)+1),2)),0)
Thank you in advance.
AW
Please can you help me combine these formulas with an IF statement. I am looking for 4 new formulas (1 per category).
Row 1 qty:
I want it to say if column G equals 'FamilyA' then do this calculation =IFERROR(INDEX(sheet1!$D$2:$D$10000,MATCH(TRUE,EXACT(C2,sheet!$C$2:$C$10000),0)),0)
Or if it equals 'FamilyB' do this calculation
=IFERROR(INDEX(sheet2!$F$2:$F$10000,MATCH(TRUE,EXACT(C2,sheet2!$A$2:$A$10000),0)),0)
Row 1 date:
If column G equals FamilyA do this
=IFERROR(INDEX(sheet1!$D$2:$D$10000,MATCH(TRUE,EXACT(C2,sheet1!$C$2:$C$10000),0)),0)
Or if column G equals FamilyB do this
=IFERROR(INDEX(sheet2!$E:$E,MATCH(TRUE,EXACT(C2,sheet2!$A:$A),0)),0)
Row 2 qty:
If column G equals FamilyA do this
=IFERROR(INDEX(sheet1$D$2:$D$10000,SMALL(IF(C2=sheet1!$C$2:$C$10000,ROW(sheet1!$C$2:$C$10000)-ROW(sheet1!$C$2)+1),2)),0)
Or if column G equals FamilyB do this
=IFERROR(INDEX(sheet2!$F$2:$F$10000,SMALL(IF(C2=sheet2!$A$2:$A$10000,ROW(sheet2!$A$2:$A$10000)-ROW(sheet2!$A$2)+1),2)),0)
Row 2 date:
If column G equals FamilyA do this
=IFERROR(INDEX(sheet1!$G$2:$G$10000,SMALL(IF(C2=sheet1!$C$2:$C$10000,ROW(sheet1!$C$2:$C$10000)-ROW(sheet1!$C$2)+1),2)),0)
Or if column G equals FamilyB do this
=IFERROR(INDEX(sheet2!$E$2:$E$10000,SMALL(IF(C2=sheet2!$A$2:$A$10000,ROW(sheet2!$A$2:$A$10000)-ROW(sheet2!$A$2)+1),2)),0)
Thank you in advance.
AW