I'm trying to find a way to fill two columns in one table with two rows of data from another table based on matched criteria.

To be more specific, A3:D contains the data I'd like to pull from. I'm trying to find a formula that would:

  1. Look for values in A3:A that match the values from $F8.
  2. Populate $G5:$H8 with the contents of columns C and D. (The matched row and the next row.)
  3. Include additional two rows from columns C and D if there are multiple matches found in A3:A

I'm currently using the following formula =OFFSET($A$3,match($F8,A:A,0)-3,2,2,2) which is returning some of the data I need, but only for the first match. If there is another cell in column A that matches $F8, the values from C:D are not returned.

Here is the link to a sample for reference (which includes a "Goal" tab to show what I'm trying to display): https://docs.google.com/spreadsheets/d/1WYy-oSptjKl2YUIES8MML1Hzv39_MOXi55-m4TAJJ-s/edit?usp=sharing

1 Answer 1


Use this filter() formula in cells G5, G9 and so on:

=filter( C$5:D$22, (A$5:A$22 = F8) + (A$4:A$21 = F8) )

  • This is perfect and so much simpler than what I was trying to do! Thank you!
    – Karyssa
    Jun 21, 2022 at 21:03

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.