Copy Values to Another Sheet When a Cell Contains Lost in Excel
Question details
Return values from one worksheet to another when a cell in column F contains the word lost.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants Sheet B to display values from column A of Sheet A for rows where column F contains lost.
- Observed behavior
- Matching values should appear on Sheet B, either aligned with their source rows or together in a compact list.
Check that the source worksheet is named Sheet A and that the values to return are in column A while the text to check is in column F. Adjust the sheet name and cell references in the formulas if your workbook uses different names or columns.
Use IF and SEARCH to return values by row
Use this formula when you want each result to stay aligned with its corresponding source row.
On Sheet B, select cell A1 and enter =IF(ISNUMBER(SEARCH("lost",'Sheet A'!F1)),'Sheet A'!A1,"").
Drag the fill handle at the bottom-right corner of A1 down column A for as many source rows as you need to check.
Review column A on Sheet B. Rows whose corresponding Sheet A column F cell contains lost display the column A value; other rows appear blank.

Use FILTER to create a compact list in Microsoft 365
Use FILTER to return all matching values together without blank rows between them.
Return matching values between sheets in WPS Spreadsheet
Use IF and SEARCH in WPS Spreadsheet to display values from one sheet on another when the corresponding cell contains lost.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Enter the formula: Select A1 on Sheet B and enter =IF(ISNUMBER(SEARCH("lost",'Sheet A'!F1)),'Sheet A'!A1,"").
- 3. Fill down: Drag the fill handle from A1 down to check the remaining rows on Sheet A.
- 4. Save the workbook: Select Save or Save As to keep the formula and results in your workbook.

Frequently Asked Questions
Does the formula match Lost and LOST as well as lost?
Yes. SEARCH ignores case. Replace SEARCH with FIND in the formula if you need a case-sensitive match.
Why are there blank rows in the results?
The IF formula preserves the source row positions and returns an empty string for nonmatching rows. In Microsoft 365, use FILTER to collect matches into a compact list.
What if my source sheet has a different name?
Replace 'Sheet A' in both references with your actual source sheet name. Keep single quotes around the name if it contains spaces.
Will the results update when the source data changes?
Yes. The formulas recalculate when referenced source cells change. To keep a fixed copy instead, copy the results and use Paste Special > Values.




