The 7 Most Useful Excel Formulas for Inventory Management

If you’re starting your own retail business, you must have a way to track your inventory. Nowadays, just pen and paper won’t cut it anymore. However, full-fledged inventory management software can be expensive.

But did you know that you could make your own using Excel?

Here are seven formulas to help you create your inventory management spreadsheet. Manage your business better without spending extra on special apps.

1. SUM

If there’s one formula you’ll use in your entire life, then SUM would be it. This function allows you to add values without selecting each cell individually.

You can save time by using this command and then choosing the cell range you want to add.

Formula: =SUM(number1,[number2],…)

• NUMBER1: This is the first value for adding. It can be any number, a cell, or even a set of cells (called a range).
• NUMBER2-255 (optional): These are the following values the function will add. Again, it can be any number, cell, or range. You can place as many as 255 values here.

2. SUMIF

This formula adds one layer of complexity to the sum function. You can use the SUMIF command if you want to filter out the cells you want to add.

For example, if you only want to find the sum of inventory sold from a specific supplier, you can use SUMIF. If you’re going further to narrow down your filter to a particular product, you can use SUMIFS.

For example, you can use this function if you want to filter both supplier and product type. Basically, SUMIFS gives you the ability to use two or more variables to filter your values.

Formula: =SUMIF(range,criteria,[sum_range])

• RANGE: These are the cells Excel will inspect to know if it will add the corresponding values.
• CRITERIA: This is the value or formula which Excel will compare against the cells under RANGE.
• SUM_RANGE (optional): These are the cells that will be added together. If left blank, the cells under RANGE are used instead.

In the picture below, you’ll see that only the amount to be paid to Abacus Technologies is added.

3. SUMIFS

Formula: =SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria20,…)

• SUM_RANGE: These are the cells to be added.
• CRITERIA_RANGE1: These are the cells that Excel will test. If the values here are true based on CRITERIA1, the corresponding SUM_RANGE cells are added.
• CRITERIA1: This is what defines CRITERIA_RANGE1. This can be a number, function, or any other value.
• CRITERIA_RANGE2, CRITERIA2,… (optional): These are additional cell ranges and criteria to filter your SUMIFS further. You can have up to 127 extra range/criteria pairs.

In the example below, only the amounts to be paid to the Jupiter Brand under the Abacus Technologies Manufacturer are added together.

If you find these formulas getting a bit complicated, here are some tips to help you learn Excel quickly.

4. LOOKUP

This function has two options: VECTOR and ARRAY. The Vector option is best for looking up data in specific columns.

The Array option, conversely, searches for values in multiple rows and columns. It has since been superseded by VLOOKUP and HLOOKUP but remains for compatibility with older spreadsheets.

Formula: =LOOKUP(lookup_value,lookup_vector,[result_vector])

• LOOKUP_VALUE: This is the value that Excel will find in your LOOKUP_VECTOR.
• LOOKUP_VECTOR: This is the range where Excel will find your LOOKUP_VALUE. This must only be a single row or column, and the values under here should be in ascending order.
• RESULT_VECTOR (optional): This is the range where Excel will find the corresponding value to return. This is based on the results of LOOKUP_VECTOR.

In the sample below, the user knows the item name but is unfamiliar with the SKU code. To get the SKU code, they used the LOOKUP formula.

5. VLOOKUP

The VLOOKUP function is helpful for data arranged in columns. Excel looks for the value you assign in the first column of your chosen range. It will then count across columns based on the number you give and return a corresponding value.

Formula: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

• LOOKUP_VALUE: This is the value that Excel will look for in the first column of your table.
• TABLE_ARRAY: This is the range of cells where the function will operate. Excel will use LOOKUP_VALUE and find it in the first column of the TABLE_ARRAY. It will then count the number of cells based on the following variable to return a result.
• COL_INDEX_NUMBER: This is the number of columns that Excel will count to return a result.
• RANGE_LOOKUP (optional): This can only be either TRUE or FALSE. If you write TRUE, it will return approximate matches (and your first column should be in ascending order). If you choose FALSE, it will return exact matches only.

In the sample below, the user searched for the price of a specific SKU. Since the price is under the 5th column, the value under COL_INDEX_NUMBER is set to 5.

6. HLOOKUP

The HLOOKUP function is almost the same as VLOOKUP, except that your data here should be arranged by row.

Formula: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

• LOOKUP_VALUE: This is the value that Excel will look for in the first row of your table.
• TABLE_ARRAY: This is the range of cells where the function will operate. Excel will use LOOKUP_VALUE and find it in the first column of the TABLE_ARRAY. It will then count the number of cells based on the following variable to return a result.
• ROW_INDEX_NUMBER: This is the number of rows that Excel will count to return a result.
• RANGE_LOOKUP (optional): This can only be either TRUE or FALSE. If you write TRUE, it will return approximate matches (and your first column should be in ascending order). If you choose FALSE, it will return exact matches only.

As shown below, the HLOOKUP formula is used to get the available stock of an SKU. Since the row number for the available inventory is 9, the ROW_INDEX_NUMBER reads 9.

7. XLOOKUP

The XLOOKUP function is the evolution of the VLOOKUP and HLOOKUP formulas. It allows you to return multiple results, and you can use it to search for both by column or by row. However, this only works with Microsoft 365 versions of Excel.

Formula: =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

• LOOKUP_VALUE: Like the formulas above, this refers to the value that Excel will find.
• LOOKUP_ARRAY: This is the range of cells Excel will comb through to find the LOOKUP_VALUE you assign. This can be located anywhere in your table but must only be a single row or column. Otherwise, your formula will return an error.
• RETURN_ARRAY: This is the results Excel will show once it finds the LOOKUP_VALUE in your LOOKUP_ARRAY. This must reflect the shape of your LOOKUP_ARRAY. For example, if LOOKUP_ARRAY is a column, the RETURN_ARRAY should also be a column. But if you choose multiple rows or columns, RETURN_ARRAY will return numerous results.
• IF_NOT_FOUND (optional): The formula will display the text your write here if it cannot find the value you seek. If left blank, this will default to #N/A.
• MATCH_MODE (optional): Can only be -1, 0, 1, or 2. -1, 0, and 1 will return an exact match. If none is found, -1 will show the next smaller value, 0 will return #N/A, and 1 will show the next larger value. The if left blank, this will default to 0.
• SEARCH_MODE (optional): Can only be 1, -1, 2, or -2. 1 starts the search from the first item in your LOOKUP_ARRAY, while -1 starts from the last. 2 executes a binary search, which requires your LOOKUP_ARRAY to be in ascending order. -2 does the same, but your list must be in descending order. Both 1 and -1 are good for short lists, but if you have a lot of data that you can sort, consider using 2 or -2.

In the following example, you can see how you can pull out the complete information of a single item by simply typing the item name. You’re also not limited to choosing the first column or row of your table—you can pick any dataset to look through.

There’s a Lot More to Excel Than You Might Expect

These are some of the formulas you can use to help you manage your inventory. If you want to use Excel in other aspects of your life, these formulas can help you solve real-life problems.

Knowing how to use these functions and a little formatting can get your inventory management system to go a long way.