Find Stock Level by SKU
I need to look up the current stock quantity for a specific product SKU from a large inventory table.
=XLOOKUP(F2,A2:A6,C2:C6,"SKU Not Found") How it works: This XLOOKUP formula efficiently searches for the SKU specified in cell F2 within the range A2:A6 (your SKU column). Once it finds a match, it returns the corresponding value from the range C2:C6 (your Current Stock column). The optional last argument, "SKU Not Found", provides a user-friendly message if the SKU is not found in your inventory list, preventing a #N/A error.
Data Setup
| SKU | Product Name | Current Stock | Location |
|---|---|---|---|
| P-001 | Widget A | 150 | Warehouse 1 |
| P-002 | Gadget B | 75 | Warehouse 2 |
| P-003 | Doodad C | 200 | Warehouse 1 |
| P-004 | Thingamajig D | 30 | Warehouse 3 |
| P-005 | Gizmo E | 120 | Warehouse 2 |
| SKU to Find: | P-002 | ||
| Stock Level: |
Step-by-Step Guide
Enter your inventory data into cells A1:D6. Ensure 'SKU' is in column A and 'Current Stock' is in column C.
In cell F2, type the specific SKU you want to look up (e.g., "P-002"). This will be your lookup value.
In cell G2, enter the XLOOKUP formula: `=XLOOKUP(F2,A2:A6,C2:C6,"SKU Not Found")`.
Press Enter. Cell G2 will now display the current stock level for the SKU entered in F2, or 'SKU Not Found' if no match is found.
Explore More
Identifying Supplier Contact Persons
As an Inventory Manager, I frequently need to contact suppliers regarding stock levels, new orders, or delivery issues. I have a list of suppliers with their unique IDs, but often need to quickly find the specific contact person for a given supplier (e.g., from cell E2). Manually searching through a large supplier list is inefficient and can delay critical communications.
Extracting All Details for a Specific Item
An Inventory Manager needs to quickly retrieve all associated details (Description, Quantity, Location) for a specific Item ID. Manually copying and pasting multiple cells for each item is tedious and error-prone, especially when managing a large catalog.
Categorize Products by SKU Prefix
I need to extract the first 2 letters of an SKU (e.g., 'FU' from 'FU-101') to determine the product category (Furniture).
Trigger Reorder Alert
I need a formula that displays 'Order Now' if the quantity in stock is less than 10, otherwise displays 'Stock OK'.