0

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

Improve this question

1 Answer 1

Reset to default
0

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) )

Improve this answer
1
  • This is perfect and so much simpler than what I was trying to do! Thank you! Commented Jun 21, 2022 at 21:03

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.