Extracting data based on multiple criteria in Google Sheets

Other Questions Google Sheets
1

Hey everyone! I’m trying to figure out how to pull specific data from my Google Sheet. I need a formula that can grab info from columns C to F, but only if two things match up:

  1. The value in column H matches the value in column A
  2. The value in column I matches the value in column B

If there’s no match, I want the row to stay empty. I’ve been scratching my head over this for a while now. Any ideas on how to make this work? I’m not great with complex formulas, so a simple explanation would be super helpful. Thanks in advance for any tips!

2

hey nate, sounds like u need VLOOKUP or INDEX/MATCH combo. try this:

=IF(AND(H2=A2,I2=B2),INDEX(C2:F2,1,1),“”)

drag it down. it’ll grab C2:F2 if H and I match A and B. empty if not. hope that helps!

3

Have you considered using QUERY function? It’s quite powerful for tasks like this. Here’s a formula that might work:

=QUERY({A:I}, “SELECT C,D,E,F WHERE H = A AND I = B”, 0)

This essentially creates a virtual table from A to I, then selects columns C to F where H matches A and I matches B. The ‘0’ at the end suppresses headers.

If you need to reference specific cells instead of entire columns, just adjust the ranges accordingly. For large datasets, QUERY can be more efficient than VLOOKUP or INDEX/MATCH.

Remember to double-check your data types. If you’re comparing dates or numbers, ensure they’re in the same format across columns.

4

I’ve actually dealt with a similar situation before in my work. What worked for me was using an ARRAYFORMULA combined with FILTER. Here’s a formula you could try:

=ARRAYFORMULA(IF(ROW(C:C)=1,{“C”,“D”,“E”,“F”},FILTER(C:F,H:H=A:A,I:I=B:B)))

This should populate the entire range at once. It checks if the values in H and I match A and B respectively, then pulls the corresponding data from C to F. The IF statement at the beginning adds headers to keep things organized.

Just a heads up - this might slow down your sheet if you’re working with a large dataset. If that happens, you might need to look into more optimized solutions or possibly use Apps Script for better performance.