Pocket the simple and memorable way to calculate unit price in Excel, boost your work efficiency.

The role of calculating unit price in Excel plays a significant role in inventory and sales management. Knowing how to calculate unit price in Excel will help you work faster and more professionally. If you also have the same question, this article will introduce you to some simple and easy-to-remember ways to calculate unit price in Excel with illustrated examples. Let’s check it out!

When do you need to calculate unit price in Excel?

Before looking at how to calculate unit price in Excel, we need to know in which cases we need to calculate unit price. In businesses, we calculate unit price when we need to determine the price of a product in a price list table or when we need to analyze sales for a business.

What is the VLOOKUP function?

This is a useful Excel tool that helps you determine the position and extract data from an existing table vertically in a professional and efficient manner. You can use the VLOOKUP function independently or in combination with other functions in Excel. Therefore, this function is one of many popular Excel reference tools that help users easily search for data in a spreadsheet.

The formula for using the VLOOKUP function

The formula for the VLOOKUP function includes the following elements:

• The function name “VLOOKUP” to search for the value
• The range of data
• The column index number
• The lookup range

The formula for the VLOOKUP function is as follows:

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

Where:

• lookup_value: The value to be looked up.
• table_array: The range of cells that contains the values you are looking for.
• col_index_num: The column number to retrieve data from within the range that contains the values.
• [range_lookup]: A logical value (TRUE = 1, FALSE = 0) that Excel uses to determine whether to look for an approximate match or an exact match within the specified range.

[range_lookup] = 1 (TRUE): Approximate match.

[range_lookup] = 0 (FALSE): Exact match.

If this argument is omitted, Excel defaults to [range_lookup] = 1.

How to calculate unit price in Excel

There are many functions that you can use to calculate unit price in Excel, such as the IF function, the VLOOKUP function,… Let’s explore in more detail how to calculate unit price in Excel with the following illustrated example!

We have a Sales Statistics Table and a Price List Table as shown in the image. We will use Excel functions to extract the unit price from the Price List Table to the Sales Statistics Table.

How to calculate unit price in Excel using the IF function

With the IF function, you can calculate unit price in Excel using the following formula:

=IF(AND(B5=$A$13, A5=$B$12), $B$13, IF(AND(B5=$A$13, A5=$C$12), $C$13, IF(AND(B5=$A$13, A5=$D$12), $D$13, IF(AND(B5=$A$13,A5=$E$12),$E$13))))

How to calculate unit price in Excel using the VLOOKUP and MATCH functions

In cases where you need to reference and extract a unit price that has two or more conditions, you can apply a combination of the VLOOKUP and MATCH functions. To use the calculation method of unit price in Excel using the VLOOKUP and MATCH functions, you need to understand how the VLOOKUP function works based on which value and the MATCH function works based on which value.

To identify the value, we need to pay attention to the structure of the Price List Table:

• The Company Code is in the first column of the Price List Table. With the VLOOKUP function being a vertical lookup function, the VLOOKUP function will search according to the Company Code.
• The purpose of using the VLOOKUP function is to retrieve the correct unit price corresponding to the item code, located in which column of the table. Therefore, we will combine using the MATCH function to consider the Item Code value.

So, we have the structure as follows:

=VLOOKUP(Company Code, Price List Table, MATCH(Item Code, row containing Item Code, 0), 0)

Based on the given data table, we have the formula:

=VLOOKUP(B5,$A$13:$D$15,MATCH(A5,$A$12:$D$12,0),0)

How to calculate unit price in Excel using the HLOOKUP and MATCH functions

The next method to calculate unit price in Excel is to use a combination of the HLOOKUP and MATCH functions. The HLOOKUP function has a similar usage to the VLOOKUP function, but while VLOOKUP searches for information vertically, the HLOOKUP function searches horizontally.

• The value that the HLOOKUP function searches must belong to the first row of the table to be searched. Looking at the data table, we see that the first row of the Price List Table is the Item Code. Therefore, we will use the HLOOKUP function to search according to the Item Code.
• The HLOOKUP function will produce results based on the Company Code to determine the number of rows to take the corresponding value. Therefore, we will use the MATCH function to search according to the Company Code.

So, we have the structure as follows:

=HLOOKUP(Item Code, Price List Table, MATCH(Company Code, column containing Company Code, 0), 0)

Based on the given data table, we have the formula:

=HLOOKUP(A5,$B$12:$D$15,MATCH(B5,$A$12:$A$15,0),0)

How to calculate unit price in Excel using the INDEX and MATCH functions

The VLOOKUP and HLOOKUP functions are helpful tools that can assist you in calculating unit price in Excel. However, there will be cases where the VLOOKUP function is not able to solve the problem when calculating unit price. In this case, you have another option, which is to use a combination of the INDEX and MATCH functions.

Using the INDEX and MATCH functions in a smart combination can produce better results than using the VLOOKUP function in many cases, helping you work efficiently, quickly, and with fewer errors. In addition, the INDEX and MATCH functions are very flexible and can be customized for different situations. Let’s learn how to use INDEX MATCH.

When calculating with the INDEX MATCH functions, there are 3 elements that you need to pay attention to:

• The data range to be searched: When calculating unit price, choose the range B13:D15.
• The row within the array containing the value: Identify the row using the MATCH function, search according to the Company Code.
• The column within the array containing the value: Identify the column using the MATCH function, search according to the Item Code.

So, we have the structure as follows:

=INDEX(Data range, MATCH(Company Code, Column containing Company Code, 0), MATCH(Item Code, row containing Item Code,0))

Based on the given data table, we have the formula:

=INDEX($B$13:$D$15,MATCH(B5,$A$13:$A$15,0),MATCH(A5,$B$12:$D$12,0))

How to calculate unit price in Excel using the SUMPRODUCT function

The SUMPRODUCT function is also a flexible tool that you can use to calculate unit price in Excel based on multiple conditions, and it can also be used to calculate in many other situations. Let’s learn about the SUMPRODUCT function!

• When to use the calculation method of unit price in Excel with SUMPRODUCT: When the Price List Table does not contain duplicate item codes.
• How to use: Consider 2 conditions in the form of a 2-dimensional array: Item Code (range B12:D12) and Company Code (range A13:A15), and fetch the result in range B13:D15.

Based on the given data table, we have the formula:

=SUMPRODUCT(($A$13:$A$15=B5)*($B$12:$D$12=A5)*$B$13:$D$15)

Frequently Asked Questions

How do you calculate unit price for products with different sizes and weights?

=> You can use the IF function to analyze and check different conditions, and then apply the appropriate formula to calculate for each case, including calculating unit price.

How to verify the accuracy of calculated unit price with Excel?

=> To verify and ensure accuracy, compare the calculated unit price with Excel with other unit prices or check against the input data. Checking the formula and calculation syntax with Excel to avoid mistakes is also very important.

How to calculate unit price in Excel when there are multiple similar products?

=> In this case, use the SUMPRODUCT function to calculate the total value of all products in the data table, then divide by the quantity of products to get the average price.

What tools are there to calculate unit price?

=> In addition to Excel, there are many other useful tools that you can use to calculate unit price, such as inventory management software, accounting software, online applications like Zoho Sheets, Smartsheet, or Google Sheets…

Conclusion

Above are some quick and convenient ways to calculate unit price in Excel. We hope that from the above information, you will choose the simplest and easiest way to calculate unit price in Excel, contributing to increasing your work productivity!

To complete office tasks such as Word or Excel smoothly and efficiently, the device you are using is also very important. Visit FPT Shop now to refer to many affordable genuine laptop models!

Laptop for office work at good prices

• How to use Excel: 5 most basic functions for beginners
• Discover detailed 4 commonly used search methods in Excel from basic to advanced

You may also like

5 Essential Excel Functions for Beginners

Master the fundamentals of Excel with these 5 basic functions! Learn how to use SUM, AVERAGE, COUNT, MIN, and MAX in order to make calculating and analyzing data easier and more efficient.

A Simple Method for Adding Watermark to Excel for All Spreadsheets in No Time

Watermarking in Excel is a great way to mark your ownership and protect the content on your Excel spreadsheets. If you’re unsure how to do it, you can follow the steps outlined in this article.

How to recover unsaved or overwritten Excel files effectively: a sure-fire method

Knowing how to recover unsaved Excel files can be extremely helpful in cases when your computer unexpectedly shuts down or you accidentally click on “Don’t save” when Excel asks if you want to save your changes before closing. Follow this article to learn the step-by-step process of how to accomplish it.

5+ Effective Ways to Convert PDF to Excel Quickly, Useful Office Tricks

Knowing the ways to convert PDF files to Excel below will enable you to easily edit and add content inside. Office workers and students definitely must know this trick if they want to simplify their tasks.