How to generate a category key in a table using the VLOOKUP function?

Microsoft Excel is one of the most complete programs to create and edit spreadsheets. This program has many features and editing tools that help us optimize the time and editing of a . One of the functions that we can use is the “LOOKUP” function, which will allow us to find data within a data table or within a spreadsheet.

In addition, the VLOOKUP function allows us to create a category using a formula with allied functions , and to create a category by relating a data table to another data table, by using nested functions in a formula in this application.

Index(  )

1. How to generate a category key in a table using the VLOOKUP function?
   1. From the Excel desktop app
   2. In the Excel mobile app
2. What characters should I use for a data category to become a key?
3. How to apply formula from one cell to the rest of the column quickly?

How to generate a category key in a table using the VLOOKUP function?

To generate a key category in a table using the VLOOKUP function , it is necessary that within the Excel file, you have two data tables that, when compared, allow you to find a certain category . Categories are created from a common data or characters that two or more data table tables have in common.

An example would be, when you want to generate the category of a certain group of people. In the first table you will find a series of data where in column A are all the names of the person of that group of people.

Next to your name, you have one of the following characters “NI, AD and ADU”, depending on your age range. Example, “Lucas – NI”, “Ana – AD”, “Julio – ADU”. In column B, it is where you must generate the category that you want to generate.

In the other data table you will find two columns. First, column D will contain the abbreviation of the category, which in this case would be the reference characters. That is “NI” for child, “AD” for adolescent and “ADU” for adult. In column E, there will be the cells where you will specify the categories of that group of people , that is, “Child”, “Adolescent” and “Adult”.

From the Excel desktop app

1. Open the Microsoft Excel application on your computer.
2. Select the file you want to edit.
3. Using the previous example, you must locate yourself in the first cell of column B. There you must place the formula that will generate the category.
4. Once you have located the cell, you must enter the search function followed by the argument of the value you want to search for.
5. The argument of the formula will be a combination of several functionsthat will help you generate the category.
6. For this example within the argument, we will use the “RIGHT” function , which indicates that the values ​​to be evaluated are written from right to left.
7. Next, we will use the “LENGTH” function, which will evaluate the length of the cell’s text behavior.
8. We will also use the “FIND” function, which will allow us to find the number of characters, where a specific character is found.
9. In the first cell of column B, is where we are going to generate the category, and we will place the following formula, =VLOOKUP(RIGHT(A2,LENGTH(A2)-SEARCH(‘-‘,A2)),$D$2:$ E$4.2,FALSE).
10. When entering the formula, the category of the first cell of column A is automatically generated.

In the Excel mobile app

1. Open the Microsoft Excel app on your mobile device.
2. Sign in with your Microsoft account .
3. Then you will have to select the Excel file that you want to edit.
4. Press the pencil icon to be able to edit the file.
5. Using the example above, you ‘ll need to be in the first cell of column B. Which is the column where you are going to generate the category of the data in column A.
6. Then you will have to enter the formula that will allow you to generate the category.
7. Start by entering the “VLOOKUP” function, and after that you must enter the formula argument, which will have the combination of several functions. The RIGHT, LENGTH, SEARCH functions.
8. Following the example, in cell B2 you would write the following formula =VLOOKUP(RIGHT(A2,LEN(A2)-SEARCH(‘-‘,A2)),$D$2:$E$4,2,FALSE).
9. Entering the formula automatically generates the category for cell A2.

What characters should I use for a data category to become a key?

To generate a category between two or more data tables, you need to use as reference characters that all the tables you want to compare have in common. This character can be numbers or letters, which are found in all the tables to be compared.

It is important that these characters are common data in the data tables to be compared so that when using the VLOOKUP function, the Excel program can generate the category you are looking for.

For a category to become a key you must use between 1 to 3 characters approximately. Although the application allows you to use more characters to generate a category, it is best to use fewer characters and thus avoid confusion when applying the formula to generate a category.

How to apply formula from one cell to the rest of the column quickly?

Although we are experts handling Excel files, it can be tedious and time consuming, having to manually fill in the same formula in the rest of the cells of the column. But you should know that this can be done in a faster way, since you will only need to apply the fast fill.

Sometimes the Excel algorithm detects that you need to apply a fast fill and when you try to fill a second cell it suggests a fast fill. But sometimes this does not usually happen, in case you will need to do the quick fill manually.

To do a quick fill of a data table column in Excel, you need to select the first cell of the column, and then you need to drag the cell down until you reach the same number of cells corresponding to the data table. This way you can fill in the rest of the cells in the column more quickly.