通过两个唯一字段获取Google Sheet整行数据求助

Hey there! I see you've been grinding on this Google Sheets problem for hours without luck—let's break this down and get you the solution you need. Based on your data structure, you want to pull full rows using two unique identifying fields, right? Here are the most straightforward methods tailored to your setup:

1. Use FILTER to Get All Matching Rows #

Since your sample data shows multiple entries under the same WEEK + CATEGORY pair, the FILTER function is perfect—it returns every row that meets both your conditions.

Assuming your data lives in columns A to E (with WEEK in A, CATEGORY in B), here's the formula to grab all rows where WEEK=1 and CATEGORY="CAT1":

=FILTER(A:E, A:A=1, B:B="CAT1")

• Replace 1 and "CAT1" with your actual target values, or use cell references (like G1 and H1) to make it dynamic.
• This will spill all matching rows automatically, no need to drag the formula down.

2. Use QUERY for Flexible Customization #

If you want more control (like only returning specific columns, or adding extra filters), the QUERY function is your go-to.

To fetch full rows for WEEK=1 and CATEGORY="CAT1":

=QUERY(A:E, "SELECT * WHERE A=1 AND B='CAT1'", 1)

• The SELECT * means "return all columns"—swap this with SELECT A,C,D if you only need specific columns.
• The final 1 tells the function your data has a header row (adjust to 0 if there's no header).

3. Use XLOOKUP for Exact Unique Pairs #

If your two fields form a truly unique combination (e.g., WEEK + TIMESTAMP), XLOOKUP will pull the single matching row cleanly:

=XLOOKUP(1&"2024-05-20", A:A&E:E, A:E)

• Here, we're combining the two unique fields into a single key (1&"2024-05-20") to search against the combined values of columns A and E.
• This works great when you know there's exactly one row matching your two fields.

All these methods work directly in Google Sheets, no external tools needed. Just adjust the column references and condition values to match your exact data setup!

内容的提问来源于stack exchange，提问作者Matt Rogers