Function Problems

Copy Values to Another Sheet When a Cell Contains Lost in Excel

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Return values from one worksheet to another when a cell in column F contains the word lost.

Copy Values to Another Sheet When a Cell Contains Lost in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

On Sheet B, select cell A1 and enter =IF(ISNUMBER(SEARCH("lost",'Sheet A'!F1)),'Sheet A'!A1,"").

2
Fill the formula down

Drag the fill handle at the bottom-right corner of A1 down column A for as many source rows as you need to check.

3
Check the results

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 IF and SEARCH to return values by row
Matching behavior: SEARCH is case-insensitive and matches lost anywhere in the cell, including within longer words. Replace SEARCH with FIND if the match must be case-sensitive.
Work with spreadsheets in WPS Office

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Enter the formula: Select A1 on Sheet B and enter =IF(ISNUMBER(SEARCH("lost",'Sheet A'!F1)),'Sheet A'!A1,"").
  3. 3. Fill down: Drag the fill handle from A1 down to check the remaining rows on Sheet A.
  4. 4. Save the workbook: Select Save or Save As to keep the formula and results in your workbook.
Return matching values across worksheets with a familiar formula.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface to fill formulas down.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.