How to Automatically Populate Plant Life Forms from ID Codes in WPS Spreadsheets
Question details
The user needs a spreadsheet lookup method that automatically returns the plant life form associated with each plant identification code.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a plant inventory or biological dataset in a spreadsheet, where one column contains plant ID codes and another column should display the matching life form.
- Observed behavior
- The life form column currently remains blank instead of populating automatically from the plant ID codes.
Make sure your spreadsheet has a Plant ID column and a separate lookup table or range containing Plant ID and Life Form columns. Confirm that the plant IDs in the source data exactly match the lookup values, including spaces and capitalization.
Use XLOOKUP to populate plant life forms from ID codes
Best for current WPS Spreadsheets versions that support XLOOKUP; it returns the matching life form and includes a built-in fallback for missing codes.
XLOOKUP is the modern lookup method. It searches the Plant ID column in your lookup table and returns the corresponding Life Form value. Because the not-found argument is built in, you do not need to wrap the formula in IFERROR.
In a separate area or sheet, create columns for Plant ID and Life Form. Enter each plant code and its matching life form, select the range, then click Insert > Table and name the table LookupTable.
Select the cell next to the first plant code, for example B2, and enter =XLOOKUP(A2,LookupTable[Plant ID],LookupTable[Life Form],""). Press Enter.
Drag the fill handle from the formula cell down through all plant ID rows, or double-click the fill handle, to populate the matched life form for every code.
Use VLOOKUP with IFERROR as a fallback lookup
Use this when XLOOKUP is unavailable or when you need to share the workbook with users on older spreadsheet versions.
Populate plant life forms from ID codes in WPS Spreadsheets
WPS Spreadsheets can automatically return the life form for each plant ID using XLOOKUP or VLOOKUP. Create a lookup table, enter a lookup formula, and copy it down to update your plant inventory in seconds.
- 1. Open WPS Spreadsheets: Launch WPS Office and open a new or existing plant inventory workbook in WPS Spreadsheets.
- 2. Create the lookup table: Click the + sheet icon to add a sheet, then enter Plant ID and Life Form columns. Select the range and click Insert > Table.
- 3. Enter the lookup formula: In the cell next to the first plant ID, type =XLOOKUP(A2,LookupTable[Plant ID],LookupTable[Life Form],"") and press Enter.
- 4. Copy the formula down: Drag the fill handle down through all plant ID rows to auto-populate every life form.
- 5. Save the workbook: Click Menu > Save As and choose a Microsoft Excel-compatible .xlsx format if needed.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP?
Yes. Current WPS Spreadsheets versions support XLOOKUP. If your version does not show XLOOKUP, use the fallback formula =IFERROR(VLOOKUP(A2,LookupTable,2,FALSE),"").
What should I do if a plant ID code is missing from the lookup table?
The formula returns an empty string when the not-found argument is "". To show a custom message instead, replace "" with "Not found" inside the formula.
Can I use the lookup table on another sheet?
Yes. Place the lookup table on a separate sheet, select it, and create a table named LookupTable. The formulas can reference LookupTable[Plant ID] and LookupTable[Life Form] across sheets.
How do I copy the formula down without changing the lookup range?
If you use an Excel table named LookupTable, the structured reference stays fixed automatically. For a normal range, lock the lookup range with absolute references such as $B$2:$C$100.
