Excel Formula to Return the Last Value by Customer
Question details
Return the last value in the columns associated with each customer name.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Customer names in A22:A31 need to be matched to headers in row 3, with results returned from rows 4:13 and the adjacent column.
- Observed behavior
- Each customer should have the last available date or amount from the matching column and its adjacent column displayed beside the name.
Confirm that customer names appear in row 3 and that the source values are in rows 4:13. Dates and amounts must be stored as numbers, not text, for the LOOKUP formula to return them.
Find the last numeric value in each customer column
Use this formula when the values to return are numeric, such as Excel dates or amounts.
MATCH finds the customer header, INDEX selects its source column, and LOOKUP returns the last numeric value in that column. IFERROR displays a blank if no match or numeric value is found.
In C22, enter =IF($A22="","",IFERROR(LOOKUP(9.99999999999999E+307,INDEX($4:$13,0,MATCH($A22,$3:$3,0))),"")) and press Enter.
In D22, enter =IF($A22="","",IFERROR(LOOKUP(9.99999999999999E+307,INDEX($4:$13,0,MATCH($A22,$3:$3,0)+1)),"")) and press Enter. The +1 selects the column immediately to the right of the customer header.
Select C22:D22 and drag the fill handle down through row 31. If a returned date appears as a number, select the result cells and choose a date format from Home > Number.

Find the last customer value in WPS Spreadsheets
Use the same MATCH, INDEX, and LOOKUP formulas in WPS Spreadsheets to retrieve the last numeric date or amount from each customer's columns.
- 1. Open your workbook: Open WPS Spreadsheets and choose Open to select the workbook containing the customer headers and values.
- 2. Enter the lookup formulas: Enter =IF($A22="","",IFERROR(LOOKUP(9.99999999999999E+307,INDEX($4:$13,0,MATCH($A22,$3:$3,0))),"")) in C22. Enter the same formula in D22 with +1 after MATCH($A22,$3:$3,0) to search the adjacent column.
- 3. Fill and format results: Select C22:D22 and drag the fill handle through row 31. Use the Home tab's number format controls to display date results as dates.
- 4. Save the workbook: Choose Menu > Save As and select an Excel workbook format such as XLSX.

Frequently Asked Questions
How does the formula find the last value?
LOOKUP searches the selected column for a very large number and returns the last numeric value when that number is not present. INDEX supplies the column found by MATCH.
Will the formula return text in the last non-empty cell?
No. This LOOKUP formula returns the last numeric value and ignores text. Dates must be stored as Excel date numbers rather than text.
Why is the result blank?
The formula returns a blank when A22 is empty or IFERROR catches an error, such as a missing customer header or no numeric value in the selected column. Check the customer name in row 3 and the values in rows 4:13.
Do the date and amount always come from the same row?
No. The C22 and D22 formulas search their respective columns independently. If the last numeric entries occur on different rows, the results will come from different rows.




