WPS Spreadsheets: Use Two Criteria to Return Multiple Columns
Question details
The user needs to match a date and receipt number across two files and return all matching columns from File 2.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- File 1 contains lookup values, while File 2 contains the date, receipt number, and return data in columns A through P.
- Observed behavior
- The desired result is to return every row where both criteria match and display “Not found” when no exact match exists.
Confirm that the date values use the same date format in both files and that receipt numbers do not contain unintended spaces. Identify the data range in File 2, including columns A through P and the two criteria columns.
Use FILTER with two criteria to return matching columns
Use this dynamic-array formula when WPS Spreadsheets supports FILTER and you need every matching row from File 2.
The multiplication operator combines two logical tests with AND logic: both the date and receipt number must match. Use bounded ranges or an Excel Table instead of entire-column references for better performance.
In the example, A2 is the date in File 1 and B2 is the receipt number in File 1. Adjust the row limits to cover your File 2 data.
Open File 1 and select the cell where the returned data should begin, such as C2.
Enter =FILTER(File2!A2:P1000,(File2!A2:A1000=A2)*(File2!B2:B1000=B2),"Not found") and press Enter.
Allow the formula to spill into the empty cells to the right and below the result cell. Each matching row from File 2 will return columns A through P.
Replace A2:P1000, A2:A1000, and B2:B1000 with the actual File 2 range, or select the corresponding columns in an Excel Table.
Match Two Criteria and Return Multiple Columns in WPS Spreadsheets
WPS Spreadsheets can use the FILTER function with combined date and receipt-number tests to return every matching row from another file. Its familiar spreadsheet interface supports Excel-compatible workbooks while keeping the lookup formula easy to edit and reuse.
- 1. Open the source workbook: Open File 1 and File 2 in WPS Spreadsheets, then confirm the date and receipt-number columns in File 2.
- 2. Select the output cell: In File 1, click the cell where the returned columns should start.
- 3. Insert the lookup formula: Enter =FILTER(File2!A2:P1000,(File2!A2:A1000=A2)*(File2!B2:B1000=B2),"Not found") and press Enter.
- 4. Check the returned records: Review the spilled rows and expand the source range if File 2 contains more than 1,000 data rows.
Frequently Asked Questions
How do I match both a date and a receipt number in WPS Spreadsheets?
Use FILTER with two multiplied conditions, such as =FILTER(File2!A2:P1000,(File2!A2:A1000=A2)*(File2!B2:B1000=B2),"Not found"). The multiplication requires both conditions to be true.
Can the formula return more than one matching row?
Yes. FILTER returns every row that meets both criteria and spills the results into adjacent cells. Make sure the spill area is empty before entering the formula.
Why does the formula show “Not found” even though the values look identical?
Check for different date values or formats, leading or trailing spaces in receipt numbers, and text-versus-number differences. Clean receipt numbers with TRIM when necessary and ensure both dates represent the same underlying value.
Can I return columns other than A through P?
Yes. Change the first FILTER range to the columns you want returned, such as File2!C2:H1000, while keeping the date and receipt-number criteria ranges aligned to the same rows.
What should I do if FILTER is unavailable in my spreadsheet version?
Update WPS Office or use a helper column that combines the date and receipt number, then use a compatible lookup formula. For multiple matching rows, newer WPS Spreadsheets versions with dynamic-array support provide the simplest approach.
