• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidating formulas with IF statements to cover 2 scenarios

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
 
A sample sheet ( no pics please) with some data and some manually expected results would really help
 
Then try:
Code:
=LET(a,SWITCH(G2,"FamilyA",Sheet1!$C$2:$D$22,"FamilyB",CHOOSE({1,2},Sheet2!$A$2:$A$22,Sheet2!$F$2:$F$22)),IFERROR(INDEX(INDEX(a,0,2),MATCH(TRUE,EXACT(C2,INDEX(a,0,1)),0)),0))
edit: no I've made a mistake, hang on… now corrected.
[you'll need to replace all the 22s with 10000]
Just be way of explanation, the part:
CHOOSE({1,2},Sheet2!$A$2:$A$22,Sheet2!$F$2:$F$22))
is a way of bringing the two columns next to each other, column A the lookup, column F the return.
I didn't need to do it for Sheet1 (where it shows as Sheet1!$C$2:$D$22) since they were already next to each other; pedantically that part could have been:
CHOOSE({1,2},Sheet1!$C$2:$C$22,Sheet1!$D$2:$D$22))

However, since you've got up to date functions there are easier formulae to give you both rows that you're after. Can you attach a workbook with these three sheets in so I can understand what you're trying to do?
 
Last edited:
Back
Top