0

In Google Sheets: First, I'd like to return the values in the whole column of D into Column H if:

  1. E and F equal A and B
  2. C is not blank

This was the formula I tried: =IF(ISBLANK(C2=TRUE),"",(ARRAYFORMULA(VLOOKUP(E2:E&F2:F,{A2:A&B2:B,C2:D},4,FALSE))))

Second, I'd like to return the values in the whole column of D in Column I if:

  1. E and F equal A and B
  2. C is blank

Here's what I tried: =IF(ISBLANK(C2=TRUE),ARRAYFORMULA(VLOOKUP(E2:E&F2:F,{A2:A&B2:B,C2:D},4,FALSE)),"")

Neither work though... What am I doing wrong? Here's an image of the spreadsheet: Both formulas are in Row 2 of H and I respectively.

2 Answers 2

0

Update

=MAP(E2:E,F2:F,LAMBDA(e,f,
    {SUM(IFNA(FILTER(D:D,A:A=e,B:B=f,C:C<>""))),
SUM(IFNA(FILTER(D:D,A:A=e,B:B=f,C:C="")))}

))
4
  • Thanks for checking it out. As with doubleunary's recommendation, this only returns one value. The only difference is that it returns the value in I2. This leads me to believe that I'm doing something wrong. I just don't know what it is. Any thoughts?
    – Dustin
    Feb 19, 2023 at 1:54
  • See update. You were trying to do some lookup to consolidate data, we understood you were doing some logical comparison row by row
    – Martín
    Feb 19, 2023 at 3:25
  • Thanks! That worked, except for a few rows. Wish I could post a pic here... here's the error though: Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 2. Actual: 1.
    – Dustin
    Feb 19, 2023 at 4:11
  • Try again to see if you may have duplicates, SUM would group them.
    – Martín
    Feb 19, 2023 at 4:17
0

To get aggregate results, use vlookup() and query(), like this:

=arrayformula( 
  if( 
    len(E2:E & F2:F), 
    iferror( 
      vlookup( 
        E2:E & F2:F, 
        query( 
          { A2:A & B2:B, isblank(C2:C), D2:D }, 
          "select Col1, sum(Col3) where Col1 is not null group by Col1 pivot Col2", 
          0 
        ), 
        { 2, 3 }, false 
      ) 
    ), 
    iferror(1/0) 
  ) 
)

The formula goes into cell H2 and will fill the whole column. Clear columns H2:I before you apply the formula to make room for the results.

(outdated): To get row-by-row results, use Boolean arithmetic, like this:

=arrayformula( 
  if( 
    (E2:E = A2:A) * (F2:F = B2:B), 
    { 
      if( not(isblank(C2:C)), D2:D, iferror(1/0) ), 
      if( isblank(C2:C), D2:D, iferror(1/0) ) 
    }, 
    iferror(1/0) 
  ) 
)

See { array expressions } and arrayformula().

2
  • Thanks for your help! For whatever reason, that only returns (Edit: just found out carriage return sends...) the first value in H, and nothing in I. I did make sure I completely cleared H and I.
    – Dustin
    Feb 18, 2023 at 22:56
  • Edited the answer. Consider showing your desired results so that we know what you are looking for. Feb 19, 2023 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.