Function Problems

How to Find Ingredients and Required or Optional Status Using Excel Formulas

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs a formula to look up a specific product (like a sandwich) in a matrix table, search its row for 'R' (Required) or 'O' (Optional), and return the matching ingredient names from the header row along with their status.

Product: Microsoft 365 Excel
Device & OS: not provided
Scenario: Generating a dynamic ingredient checklist for specific products from a master data matrix, distinguishing between required items and optional add-ons.
Observed behavior: The goal state is to output the matching ingredients and their statuses, either combined into a single cell using text functions or spilled into multiple cells as an interactive checklist.

Before you start

Ensure you are using Microsoft 365, Excel 2021, or a compatible modern spreadsheet software that supports dynamic array functions like FILTER, XLOOKUP, and LET.

Solution 1Recommended

Combine Matching Ingredients into a Single Cell Using ARRAYTOTEXT

Use this method if you want a comma-separated list of all relevant ingredients (both Required and Optional) consolidated into one cell for a clean, compact view.

This solution utilizes the XLOOKUP function to isolate the row belonging to the specific product, and the FILTER function to extract the header values where the row is not blank.

The ARRAYTOTEXT function then takes this spilled array of matching headers and converts it into a single comma-separated text string.

Select the target cell

Click on the cell where you want the combined ingredient list to appear.

Input the array formula

Assuming J2 contains your lookup product, A2:A5 is the product list, B1:H1 are the ingredient headers, and B2:H5 contains the 'R' or 'O' markers, enter: =ARRAYTOTEXT(FILTER($B$1:$H$1,XLOOKUP(J2,$A$2:$A$5,$B$2:$H$5)<>""))

Alternative using LET

To make the formula slightly more efficient by not repeating the XLOOKUP, you can use: =LET(rw,XLOOKUP(J2,$A$2:$A$5,$B$2:$H$5),ARRAYTOTEXT(FILTER($B$1:$H$1,rw<>"")))

Execute the formula

Press Enter to evaluate the formula. The ingredients will populate as a single text string.

Solution 2

Group Ingredients Specifically by Required (R) and Optional (O) Status

Best for scenarios where you need to clearly distinguish which ingredients are mandatory and which are optional in a structured text format.

Extract and Filter Data Efficiently with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like XLOOKUP, FILTER, LET, and TEXTJOIN, making it effortless to build dynamic ingredient lists and complex data extraction tools without paying for expensive subscriptions.

1. Open your matrix table: Launch WPS Spreadsheet and open your document containing the product and ingredient matrix.
2. Apply dynamic arrays: Select your target cell and use the exact same =XLOOKUP() or =FILTER() formulas provided in the solutions above.
3. Press Enter to calculate: Hit Enter. WPS Spreadsheet will automatically process the dynamic array and spill the results across cells or combine them into text just like Microsoft 365.

Full compatibility with modern Microsoft Excel (.xlsx) formulas.Native support for dynamic array spilling behavior.Built-in advanced data lookup and text manipulation functions.Lightweight, fast, and free to use.

microsoft office alternative - wps office

Frequently Asked Questions

How do I spill the ingredients into a list so users can check them off manually?

To display the results as a vertical list rather than combining them into a single cell, replace the ARRAYTOTEXT or TEXTJOIN function with TOCOL. For example: =TOCOL(FILTER($B$1:$H$1,XLOOKUP(J2,$A$2:$A$5,$B$2:$H$5)<>"")). This spills the matching ingredients into separate rows beneath your formula cell.

Why is my FILTER function returning a #CALC! error?

The #CALC! error occurs when the FILTER function returns an empty array, meaning no cells in the target row matched your criteria (e.g., there were no 'R' or 'O' values found). You can fix this by adding a third argument to FILTER to handle empty results, like this: FILTER(array, include, "No ingredients found").

Can I achieve this without using dynamic array formulas?

Yes, but it is much more complex. In older versions of Excel that lack FILTER and XLOOKUP, you would need to use complex INDEX/AGGREGATE or nested IF/MATCH array formulas confirmed with Ctrl+Shift+Enter. Upgrading to a modern spreadsheet tool like Microsoft 365 or WPS Office is highly recommended for these tasks.

Excel & Data Problems