How to Return Today's Weight and Diet-Day Value in WPS Office Spreadsheets
Question details
The user needs to retrieve the weight and diet-day value associated with today's date from an Excel tracking table using dynamic lookup formulas instead of manually adjusting OFFSET references.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A tracking table contains dates in one column, diet-day values in another, and weight values in a third; the user wants today's row values returned automatically.
- Observed behavior
- Manual OFFSET adjustments are unreliable, so the user wants a formula-driven dynamic lookup for today's date instead of adjusting references by hand.
Make sure your date cells are stored as real date values rather than text, and identify which columns contain the date, diet-day, and weight values. If today's date may be missing, decide how the formula should handle no-match results.
Use XLOOKUP to Return Today's Weight and Diet-Day Value
Use this method when your version of WPS Office Spreadsheets supports XLOOKUP; it returns the matching row directly and handles missing dates in one formula.
XLOOKUP searches a lookup range for a match and returns the corresponding value from a return range. It is more robust than OFFSET because it works by matching the date value instead of relying on a fixed row position.
Click an empty cell where you want today's weight to appear, such as D2.
Type =XLOOKUP(TODAY(),A:A,C:C,"No entry") and press Enter. Replace A:A with your date column and C:C with your weight column.
In another cell, type =XLOOKUP(TODAY(),A:A,B:B,"No entry") and press Enter. Replace B:B with your diet-day column.
Use INDEX and MATCH as an Alternative Formula
Use this fallback when XLOOKUP is not available in your version of WPS Office Spreadsheets or when you need a formula compatible with older workbook formats.
Create a Dynamic Daily Weight and Diet-Day Lookup in WPS Office
WPS Office Spreadsheets supports Excel-compatible lookup formulas, so you can replace manual OFFSET references with XLOOKUP or INDEX and MATCH and automatically return today's weight and diet-day value from your tracking table.
- 1. Open your tracking workbook in WPS Office: Launch WPS Office Spreadsheets and open the workbook that contains your date, diet-day, and weight columns.
- 2. Select the result cells: Click an empty cell for today's weight and another empty cell for today's diet-day value.
- 3. Enter a dynamic lookup formula: Type =XLOOKUP(TODAY(),A:A,C:C,"No entry") for weight and =XLOOKUP(TODAY(),A:A,B:B,"No entry") for diet day, then press Enter.
- 4. Verify the returned values: Check that the formulas return the correct row for today's date and display the expected weight and diet-day values.
Frequently Asked Questions
What formula returns today's weight if the date column is A and the weight column is C?
Use =XLOOKUP(TODAY(),A:A,C:C,"No entry"). If XLOOKUP is unavailable, use =INDEX(C:C,MATCH(TODAY(),A:A,0)).
Why does my XLOOKUP or INDEX and MATCH formula return #N/A?
It usually means today's date is not listed in the date column, or the date cells are stored as text instead of real date values. Confirm that the date column is formatted as Date and use IFERROR to return a custom message when no match exists.
Can I use VLOOKUP instead of XLOOKUP for this tracking table?
Yes, if the date column is the leftmost column in your lookup range. Use =VLOOKUP(TODAY(),A:C,3,FALSE) for weight and =VLOOKUP(TODAY(),A:C,2,FALSE) for diet day. XLOOKUP or INDEX and MATCH is more flexible when columns are not arranged this way.
Does WPS Office support XLOOKUP?
Current versions of WPS Office Spreadsheets support XLOOKUP. If it is not available in your installation, update WPS Office or use the INDEX and MATCH alternative.
When does the TODAY function update?
TODAY() recalculates when the workbook opens or when the worksheet recalculates, so the returned weight and diet-day values change automatically based on your system date.
