This tutorial explains how to look up the first and last values in a row and return their corresponding headers in Google Sheets. We’ll use XLOOKUP as the core function, along with some optional enhancements using BYROW to apply the logic across multiple rows automatically.

If you’re working with horizontally structured data—where each row contains values across time, categories, or stages—this technique can be incredibly helpful. We’ll walk through how to find and return the header (e.g., date or label) of the first and last non-blank values in each row.

Sample Data (Payment Tracker)

In the following sample data, let’s find when the first and last payment was received from each customer.

We want to look up the first and last values in each row and return their corresponding month (header) in columns H and I respectively:

Lookup First Value in a Row and Return Header

To look up the first value’s header in a row, use the following formula:

=ArrayFormula(XLOOKUP("?*", TO_TEXT(B2:G2), $B$1:$G$1, "",2, 1))

Place this formula in cell H2 and drag it down to apply it to each row.

Formula Breakdown:

• "?*": A wildcard pattern that matches any cell with one or more characters.
• TO_TEXT(B2:G2): Converts numbers to text so they can match the wildcard.
• $B$1:$G$1: The header row (static), from which we return the result.
• "": Returns blank if nothing is found.
• 2: Enables wildcard match.
• 1: Searches from left to right.

Lookup Last Value in a Row and Return Header

To look up the last value’s header in a row, use this similar formula in cell I2:

=ArrayFormula(XLOOKUP("?*", TO_TEXT(B2:G2), $B$1:$G$1, "", 2, -1))

This version uses -1 as the last argument to search from right to left instead.

Make the Formula Spill Using BYROW

To avoid dragging formulas down manually, you can use BYROW to create an array formula that applies the same logic to each row.

For First Value Header (in H2):

=BYROW(B2:G4, LAMBDA(r, ArrayFormula(XLOOKUP("?*", TO_TEXT(r), $B$1:$G$1, "", 2, 1))))

For Last Value Header (in I2):

=BYROW(B2:G4, LAMBDA(r, ArrayFormula(XLOOKUP("?*", TO_TEXT(r), $B$1:$G$1, "", 2, -1))))

Tip: Replace B2:G4 with B2:G if you want to apply this to an expanding dataset.

Formula Breakdown:

• BYROW(range, LAMBDA(r, ...)): Applies the formula to each row of the range.
• TO_TEXT(r): Converts all values in the current row to text.
• XLOOKUP(...): Finds the first/last non-blank cell and returns the corresponding header.

Additional Tip: Return the Value Instead of Header

If you want to return the value itself (not the header), just change the return range:

• In the drag-down formulas, replace $B$1:$G$1 with B2:G2.
• In the BYROW formulas, replace $B$1:$G$1 with just r.

Real-Life Use Cases

Here are some practical examples where looking up the first and last values in a row comes in handy:

• Tracking customer payments: When customer names are in the first column and their payment history is spread across columns, you can return the first and last payment dates using the header row (which contains the dates).
• Project timelines: If each row shows a project’s status across weekly or monthly columns, you can pull the first and last non-blank status entry along with the corresponding week/month.
• Attendance logs: For students or employees, you can find when someone first attended and most recently showed up—and return the corresponding date from the header.

Resources

• Google Sheets: How to Return First Non-blank Value in a Row or Column
• How to Find the Last Value in Each Row in Google Sheets
• XMATCH First or Last Non-Blank Cell in Google Sheets
• Google Sheets: Count Cells Between First and Last Non-Blanks in a Row