USING VLOOKUP FUNCTIONS

VLOOKUP is part of the functions found in Microsoft Excel. The example below uses VLOOKUP with Microsoft Excel 2010.

VLOOKUP is used to find reference data from database data, so that the data that is expected to automatically appear, and does not need to be manually entered.

It also anticipates that certain values ​​remain the same as the reference value in the database, in other words avoiding incorrect data entry.

For example, for data on the price of goods, it will be very crucial because if the price entered is wrong, it will affect income.

In the example below, there are two tables. Table 1 contains the item stock and item details (price and amount). While the second table is a table of sales of goods per month. Following are Table 1 and Table 2

Note that both in Table 1 and Table 2 there are components:

ITEM CODE, TYPE, NAME OF GOODS and PRICE

This is a data component that is very important and must have conformity.

As a note, the ITEM CODE is the code that has been contained in each item.

His condition is:

In Table 2 Sales Data, we will enter sales data, but we will condition that ONLY: DATE, ITEM CODE and AMOUNT that we will enter. The TYPE, NAME OF GOODS and PRICE components will be automatically visible when applying the VLOOKUP function. The function of the TYPE and NAME OF GOODS we identify is as a crosscheck whether it matches between the code and the type and name of the item, so the price that appears is not wrong. While TOTAL we will condition automatically by entering the formula: AMOUNT x PRICE. See the following picture.

In D6 enter the formula = VLOOKUP (C6; TABLE22; 2; FALSE)

With explanation

C6: is an ITEM CODE row (reference)

TABLE 22: is the code in Table 1 (Item Stock Table and item details)

2: is the 2nd column of Table 1 which contains TYPE

In E6 enter the formula = VLOOKUP (C6; TABLE22; 3; FALSE)

With explanation

C6: is an ITEM CODE row (reference)

TABLE 22: is the code in Table 1 (Item Stock Table and item details)

3: is the 3rd column of Table 1 containing NAME OF GOODS

In G6 enter the formula = VLOOKUP (C6; TABLE22; 7; FALSE)

With explanation

G6: is the row of ITEM CODE (reference)

TABLE 22: is the code in Table 1 (Item Stock Table and item details)

7: is the 7th column of Table 1 containing NAME OF GOODS

In H6 enter = F6 * G6 (multiplication AMOUNT and PRICE)

You just copy the VLOOKUP formula each to the next lines

RELATED ARTICLE/ARTIKEL TERKAIT