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.
=INDEX(A2:D5, MATCH("ITEM-003", A2:A5, 0), 0) How it works: This formula uses MATCH to locate the row number for 'ITEM-003' within the A2:A5 range, which returns 3. This row number is then passed to INDEX. By setting the col_num argument of INDEX to 0, we instruct it to return all columns for that specific row. In modern Excel, this will 'spill' the values 'Doodad C', '75', and 'Warehouse 1' into adjacent cells.
Data Setup
| Item ID | Description | Quantity | Location |
|---|---|---|---|
| ITEM-001 | Widget A | 150 | Warehouse 1 |
| ITEM-002 | Gadget B | 200 | Warehouse 2 |
| ITEM-003 | Doodad C | 75 | Warehouse 1 |
| ITEM-004 | Thingamajig D | 120 | Warehouse 3 |
Step-by-Step Guide
Define the full data range that contains all item details (e.g., A2:D5). This will be the array argument for INDEX.
Determine the specific Item ID you want to look up (e.g., "ITEM-003").
Identify the range where your Item IDs are listed (e.g., A2:A5). This will be the lookup_array for MATCH.
Construct the formula: =INDEX(A2:D5, MATCH("ITEM-003", A2:A5, 0), 0).
Enter the formula into a cell. In modern Excel, this will spill the entire row's data into adjacent cells.
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.
Find Stock Level by SKU
I need to look up the current stock quantity for a specific product SKU from a large inventory table.
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'.