When it comes to working with Excel, there are various formulas and functions that one needs to be equipped with in order to carry out various operations. One such formula that is widely used is the VLOOKUP formula. In this article, we will be discussing the application of the VLOOKUP formula in creating a product code or SKU in Excel.
Understanding VLOOKUP Formula
VLOOKUP, short for “Vertical Lookup” is an Excel formula that is used to search for specific information in a given table or range of cells. It works by searching for the specified value in the first column of the table and returns the value in the same row from another column of the table. This formula is extremely useful in creating a product code or SKU as it allows for a more organized and systematic approach to product identification and inventory management.
Creating a Product Code Using VLOOKUP formula
The VLOOKUP formula can be used to create a product code in Excel by combining the different elements of the product description in a specific order. The recommended order for creating a product code is:
- Product Category
- Manufacturer or Brand
- Product Name
Using the VLOOKUP formula, we can easily combine these elements by referencing different cells in a worksheet and using concatenation (&) to join them together. Let’s take a look at an example:
Step 1: Create Table with Product Elements
We start by creating a table that lists down the different product elements that we want to include in our product code. This table is created in a separate worksheet and contains the product category, manufacturer or brand, and product name.
Step 2: Create Concatenation Formula
Once we have our table with the different product elements, we can now use the VLOOKUP formula to create our product code. In this example, we will assume that the product category is in cell A2 and we want to create a product code that combines the different elements in the following order: Category, Brand, Product Name.
To create this formula we will use the following syntax:
=VLOOKUP(A2,Table1,2,FALSE)&VLOOKUP(A2,Table1,3,FALSE)&VLOOKUP(A2,Table1,4,FALSE)
Let’s break down this formula:
- =VLOOKUP(A2,Table1,2,FALSE) – searches for the value in cell A2 in the first column of Table1 and returns the value in the second column.
- & – the ampersand is used to join the different elements of the product code together.
- VLOOKUP(A2,Table1,3,FALSE) – searches for the value in cell A2 in the first column of Table1 and returns the value in the third column.
- & – the ampersand is used to join the different elements of the product code together.
- VLOOKUP(A2,Table1,4,FALSE) – searches for the value in cell A2 in the first column of Table1 and returns the value in the fourth column.
The final formula will look like this:
=VLOOKUP(A2,Table1,2,FALSE)&VLOOKUP(A2,Table1,3,FALSE)&VLOOKUP(A2,Table1,4,FALSE)
Once we have entered this formula in the cell, we can simply drag down the formula to apply it to the rest of the cells in the column.
Separating Numbers and Letters in Excel
Another commonly performed task in Excel is separating numbers and letters from a combined cell. This is especially relevant in inventory management, where product codes may contain both alphabets and numbers. Although it is possible to manually separate the numbers and letters using Excel’s text functions, there is a quicker way to achieve this.
Step 1: Create Column for Numbers and Letters
We start by creating two new columns in our worksheet – one for the numbers and another for the letters. We will assume that our combined cell is in cell A1.
Step 2: Extract Numbers and Letters Using Formulas
Once we have created our columns, we can use the following formulas to extract the numbers and letters:
=MID(A1,MIN(FIND(0,1,2,3,4,5,6,7,8,9,A1&"0123456789")),LEN(A1))
This formula extracts the numbers in the cell and returns them in the adjacent column. Similarly, we can use this formula to extract the letters:
=LEFT(A1,MIN(FIND(0,1,2,3,4,5,6,7,8,9,A1&"0123456789"))-1)
This formula extracts the letters in the cell and returns them in the adjacent column. Once we have entered these formulas in their respective cells, we can simply drag them down to apply them to the rest of the cells in the column.
FAQ
Q: Is it possible to use the VLOOKUP formula with multiple criteria?
A: Yes, it is possible to use the VLOOKUP formula with multiple criteria by creating a combination of different formulas and functions. One popular method is using the INDEX and MATCH functions together to create a dynamic lookup formula that can search for specific values in a table.
Q: Can the syntax for the VLOOKUP formula be modified or customized?
A: Yes, the syntax for the VLOOKUP formula can be customized to suit specific requirements. This can be done by modifying the different elements of the formula, such as the range of cells, column index number, and the search criteria.
Video Tutorial: How to Use the VLOOKUP Formula in Excel
Check out the following video tutorial to learn how to use the VLOOKUP formula to search for specific information in a given table or range of cells:
