Function Problems

How to Look Up Data Across Columns and Return It in One Excel Cell

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to look up and extract data across multiple columns (e.g., nine columns) and return the single existing value into one cell.

Product: Excel
Device & OS: not provided
Scenario: Only one cell in a specific row range (like G5:O5) contains data, and the user wants to pull that single value into a unified summary cell.
Observed behavior: The user wants to retrieve the single populated value across a wide column range into one target cell without manual copying.

Before you start

Determine whether the data you are trying to retrieve is formatted as text, numbers, or dates, as this will dictate which spreadsheet function is most efficient for your task.

Solution 1Recommended

Use the CONCAT Function for Text Data

If the single value across your columns is text, the CONCAT function is the fastest and easiest way to return it into one cell.

The CONCAT function joins the text from multiple ranges and/or strings. When only one cell in a given range contains data, it will simply output that one existing text string, ignoring the blank cells.

Select the target cell

Click on the specific cell where you want the combined text data to appear.

Enter the CONCAT formula

Type =CONCAT(G5:O5) into the formula bar (replace G5:O5 with your actual column range).

Execute the function

Press Enter to apply the formula and view the extracted text value.

Alternative Function: If you are using an older version of Excel that does not support CONCAT, you can use the traditional method: =CONCATENATE(G5,H5,I5...) instead.

Solution 2

Use the IF and SUM Functions for Numbers or Dates

When dealing with numeric values or dates, using a combination of IF, COUNTA, and SUM ensures accuracy and prevents zero-value errors.

Efficient Spreadsheet Solutions

Use WPS Spreadsheet to Combine and Look Up Data Easily

WPS Office provides full support for advanced functions like CONCAT, IF, and SUM. It is a highly compatible and lightweight alternative to manage large datasets effortlessly.

1. Open your spreadsheet in WPS Office: Launch WPS Office and open your .xlsx or .csv workbook.
2. Input the lookup formula: Click your target cell, type =CONCAT(G5:O5) for text or =IF(COUNTA(G5:O5)=0,"",SUM(G5:O5)) for numerical data.
3. Press Enter to execute: Hit Enter, and WPS Spreadsheet will instantly return the correct value from your columns.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Intuitive formula builder and syntax highlighting for easier data lookup.Lightweight software with fast processing for large datasets.Free built-in PDF tools and seamless cloud synchronization.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use CONCATENATE instead of CONCAT for cell ranges?

Yes, you can use CONCATENATE, but unlike CONCAT, it does not support continuous cell ranges directly. You would need to select each cell individually, like =CONCATENATE(G5, H5, I5), which can be tedious for many columns.

What happens if multiple cells in the row have data?

If you use CONCAT, the text from all filled cells in the range will be joined together into one continuous long string. If you use the SUM method, all numerical values in the range will be added together.

Why is my returned date showing as a random number?

Dates in Excel and WPS Spreadsheet are stored as sequential serial numbers. If your formula returns a number like '44500' instead of a date, right-click the cell, select 'Format Cells', and change the category to 'Date'.

Excel & Data Problems