Function Problems

How to Use Excel Formulas to Find Inventory Status and Return Data

Elise Williams Sep 25, 2026 869 views

Question details

The user needs an Excel formula to process inventory data from a linked Google Form, specifically to evaluate 'Checking In' or 'Checking Out' statuses, calculate positive or negative quantities, and return related job names and dates from the latest nonblank records.

How to Create an Excel Formula to Find Inventory Status and Return Related Data

Product: Microsoft Excel
Device & OS: not provided
Scenario: Building a dynamic inventory tracking system where check-in and check-out logs are continuously added, requiring a formula to automatically calculate current stock logic and pull associated details.
Observed behavior: Looking for the appropriate combination of functions to search downward for nonblank values, evaluate specific text statuses, and extract corresponding row data accurately.

Before you start

Ensure your inventory source data is structured in a clean tabular format with consistent headers for Status, Quantity, Job Name, and Date. Verify that your version of Excel supports dynamic arrays (like Microsoft 365 or Excel 2021) to properly utilize modern functions like FILTER and XLOOKUP.

Solution 1Recommended

Use FILTER and IF to Extract Inventory Status and Calculate Quantity

Use the FILTER function alongside an IF statement to dynamically pull out all relevant check-in and check-out records while mathematically adjusting the quantity (making check-outs negative).

Dynamic array functions like FILTER allow you to extract entire rows of data that meet specific criteria without needing complex index-matching.

By combining this with an IF statement, you can instantly evaluate whether a status is 'Checking In' (positive stock) or 'Checking Out' (negative stock) in the same formula.

Set up your status criteria

Identify the column containing your 'Checking In' and 'Checking Out' statuses (e.g., Column B) and the column with your quantities (e.g., Column C).

Write the conditional quantity formula

In a new calculation column, enter `=IF(B2:B100="Checking Out", -C2:C100, C2:C100)`. This automatically turns check-out quantities into negative numbers while leaving check-ins positive.

Apply the FILTER function

To return the Job Name and Date for non-blank inventory items, use `=FILTER(A2:D100, B2:B100<>"")`. This extracts all rows where the status column is not empty.

Use FILTER and IF to Extract Inventory Status and Calculate Quantity

Pro Tip: Format your source data as an Excel Table (Ctrl + T) so your ranges automatically expand when new form responses are submitted.

Solution 2

Use XLOOKUP to Find the Latest Status from the Bottom Up

If you only need to retrieve the most recent status, job name, and date for a specific inventory item, XLOOKUP is the most efficient choice because it can search from the bottom of your dataset upwards.

Easily Track Inventory with Advanced Formulas in WPS Spreadsheet

WPS Spreadsheet provides full support for advanced data analysis functions like XLOOKUP, FILTER, and LET. You can easily build dynamic inventory tracking systems and process form data without worrying about formula compatibility.

1. Open WPS Spreadsheet: Launch WPS Office and open your inventory tracking workbook.
2. Import Your Data: If using Google Forms, export your responses as a CSV and open them directly in WPS Spreadsheet, or paste your raw data into a new sheet.
3. Apply Dynamic Functions: Use XLOOKUP or FILTER exactly as you would in Excel to extract statuses, calculate negative quantities for check-outs, and retrieve job dates.
4. Save and Update: Save your file in standard .xlsx format. The formulas will automatically update whenever new data is added to the source columns.

100% compatibility with Microsoft Excel formulas and functionsFull support for modern dynamic arrays like FILTER and XLOOKUPFree and lightweight alternative to heavy spreadsheet softwareSeamlessly handles CSV and data imports from Google Forms

Frequently Asked Questions

Why is my XLOOKUP formula returning a #NAME? error?

The #NAME? error usually occurs if your spreadsheet software does not support the function. Ensure you are using Microsoft 365, Excel 2021, or the latest updated version of WPS Spreadsheet, all of which fully support XLOOKUP.

How can I automatically make 'Checking Out' quantities negative?

You can wrap your quantity cell reference in a simple IF statement. For example, use `=IF(B2="Checking Out", -C2, C2)`. This checks if the status in B2 is 'Checking Out' and, if true, multiplies the quantity in C2 by -1.

How do I ensure my formula includes new Google Forms submissions automatically?

Google Forms inserts new rows rather than just populating empty cells. To ensure your formulas catch new data, use entire column references (like A:A or B:B) or format your source data as an Official Table so the ranges expand automatically.

Can I use VLOOKUP instead of XLOOKUP for this inventory log?

While VLOOKUP can find matches, it searches from top to bottom and returns the first match it finds (the oldest record). For dynamic logs where the most recent entry is at the bottom, XLOOKUP or INDEX/MATCH is required to search from the bottom up.

Excel & Data Problems