Many readers want to know
about the VLOOKUP formula in Inventory Control Program. Because they want to
customize the program to better suit their needs. Many pages of the internet discussing this function in Excel software, but only a few that exemplify the application of this function.
VLOOKUP function searches for a specific reference in the first column of the table array, and return the value of data in the same row with reference data, but in different column based on index number of column.
For example: there is a list of phone number, name and address, as below image. Telephone number is used as a reference. If the yellow cell (B2) is written with telephone number, then in cell C2 will appear name, and address will appear in cell D2.
VLOOKUP function searches for a specific reference in the first column of the table array, and return the value of data in the same row with reference data, but in different column based on index number of column.
For example: there is a list of phone number, name and address, as below image. Telephone number is used as a reference. If the yellow cell (B2) is written with telephone number, then in cell C2 will appear name, and address will appear in cell D2.
In cell C2 there is a formula = VLOOKUP
(B2,B4:D7,2,0). Formula
components are described below and marked
with red fonts:
= VLOOKUP(B2, B4: D7, 2.0)
The yellow cell B2 is a reference for that formula, which contains the phone number 08111011. This phone number will be searched whose it was, and where the address.
= VLOOKUP(B2,B4:D7,2,0)
The formula will look up the same data with cell B2 in column B of the table which is written on the area or range (B4:D7).
= VLOOKUP(B2,B4:D7,2,0)
Formula finds 08111011 data in cell B6, then retrieve the data in column two (2) of the table, and the result is ‘Alex’.
= VLOOKUP(B2,B4:D7,2,0).
The zero (0) value in the latter part of the formula is named ‘range_lookup’ to determine the level of accuracy, if written 1 (one) then the formula will search for similar data to the reference data 08111011, if written 0 then the formula is only looking for the exact same data with the reference 08111011.
Seen below is the formula using range_lookup 1 to replace 0. The reference data is phone number 08111015, but no phone number on the list is ended with number 5. And the formula will look up for a similar number and found 08111012, and it is Tina’s phone number. While the VLOOKUP formula for address is still using ‘range_lookup’ zero (0), so the formula does not find a match (# N / A).
= VLOOKUP(B2, B4: D7, 2.0)
The yellow cell B2 is a reference for that formula, which contains the phone number 08111011. This phone number will be searched whose it was, and where the address.
= VLOOKUP(B2,B4:D7,2,0)
The formula will look up the same data with cell B2 in column B of the table which is written on the area or range (B4:D7).
= VLOOKUP(B2,B4:D7,2,0)
Formula finds 08111011 data in cell B6, then retrieve the data in column two (2) of the table, and the result is ‘Alex’.
= VLOOKUP(B2,B4:D7,2,0).
The zero (0) value in the latter part of the formula is named ‘range_lookup’ to determine the level of accuracy, if written 1 (one) then the formula will search for similar data to the reference data 08111011, if written 0 then the formula is only looking for the exact same data with the reference 08111011.
Seen below is the formula using range_lookup 1 to replace 0. The reference data is phone number 08111015, but no phone number on the list is ended with number 5. And the formula will look up for a similar number and found 08111012, and it is Tina’s phone number. While the VLOOKUP formula for address is still using ‘range_lookup’ zero (0), so the formula does not find a match (# N / A).
Just like other Excel software formulas, this formula can
be written directly in the cell. But to make it more clearer, below are steps to apply VLOOKUP
formula using normal procedure. In
the image below mouse (red arrow) points to
the fx (Insert Function) button of the
Formula Bar. VLOOKUP formula will be written in cell C2.
After the fx
button is pressed it will display the Insert Function window as shown below.
If the
formula does not appear in ‘Select a function’ window, type VLOOKUP in ‘Search
for a function’ window and press Go. After the VLOOKUP formula
appears in ‘Select a function’ window, seen above with blue background, press
the OK button.
Next will appear the Function Arguments window, as shown below. As ‘lookup_value’ is cell B2, this value will be searched on the table or can be named as reference value.
Next will appear the Function Arguments window, as shown below. As ‘lookup_value’ is cell B2, this value will be searched on the table or can be named as reference value.
Table_array
is a table where data will be retrieved, namely B4: D7, the title of the table may
not be included.
Col_index_num is the column position where the data is retrieved, it is second column of the table, then in the formula is written 2.
Range_lookup is 0 or FALSE, so the formula will only look up for data that is exactly the same as the reference data.
Then press the OK button and the formula will be written and function in cell C2.Do the same for cells D2 with formula to find the address of a certain phone number.
Please note that telephone numbers begin with 0 for area code or cellular number. Ensure column of phone number is formatted as Text. If formatted as General, it will be recognized as a number, so it can not be started with 0 number.
Inventory Control Program
In the Inventory Control Program Excel software sheet, VLOOKUP formula is used to retrieve the data of Description and Location, with the Part Number as a reference.
Col_index_num is the column position where the data is retrieved, it is second column of the table, then in the formula is written 2.
Range_lookup is 0 or FALSE, so the formula will only look up for data that is exactly the same as the reference data.
Then press the OK button and the formula will be written and function in cell C2.Do the same for cells D2 with formula to find the address of a certain phone number.
Please note that telephone numbers begin with 0 for area code or cellular number. Ensure column of phone number is formatted as Text. If formatted as General, it will be recognized as a number, so it can not be started with 0 number.
Inventory Control Program
In the Inventory Control Program Excel software sheet, VLOOKUP formula is used to retrieve the data of Description and Location, with the Part Number as a reference.
Seen in Formula Bar, VLOOKUP formula is using cell
B4 or Part Number as reference, formula reads the data in the table on sheet
'register' in column D through column G which is written in the formula as ‘register!D:G’.
On ‘register’ sheet, column
of retrieved data is the second column or column E, it is written as 2 in the formula.
With an accuracy that must
be exactly the same as the reference data, so it is written 0 for ‘Range_lookup’
in the formula.
The program can be used to monitor the stocks in warehouses, stores, check the prices of goods, et cetera. With slight modifications it can be used to record employee data, student data, with identification numbers as a reference.
The program can be used to monitor the stocks in warehouses, stores, check the prices of goods, et cetera. With slight modifications it can be used to record employee data, student data, with identification numbers as a reference.
Employee Timesheet
In the attached Excel workbook, there are an examples of the VLOOKUP function which is used to read the employee identification number (ID), then the formula will find and display the name of that employee in accordance with the identification number. By using the identification number, it will be much easier and faster to record working hours of employees. No need to write each name continuously, which can lead to a typo. Cells that contain formulas are blue colored.
In the attached Excel workbook, there are an examples of the VLOOKUP function which is used to read the employee identification number (ID), then the formula will find and display the name of that employee in accordance with the identification number. By using the identification number, it will be much easier and faster to record working hours of employees. No need to write each name continuously, which can lead to a typo. Cells that contain formulas are blue colored.
As
the above picture, VLOOKUP formula in cell C13 reads the employee ID in cell
B13 and write cell C13 with Margareth Isabella Taylor name. Employee ID and name are
registered in the upper left corner in e REGISTER table. This REGISTER table is
being viewed by the VLOOKUP formula to get the name of the employee based on
ID.
Data input data for employee timesheet are: date, employee ID, time to start work, time to go out for lunch, time after lunch, and time before go home. Remarks can be added if there is data that is not normal.
Table of working hours has been given Conditional Formatting, this auto formatting will change letters and numbers color for certain conditions. So time to start working (IN) will be red if later than 8:00. Clock out for lunch (OUT for lunch) will be red if sooner than 12:00. Clock in after lunch (IN after lunch) will be red if more than 13:00. And go home time (OUT) will be red if less than 17:00 hours. Total working time (TOTAL) will be red if less than 8:00.
Data input data for employee timesheet are: date, employee ID, time to start work, time to go out for lunch, time after lunch, and time before go home. Remarks can be added if there is data that is not normal.
Table of working hours has been given Conditional Formatting, this auto formatting will change letters and numbers color for certain conditions. So time to start working (IN) will be red if later than 8:00. Clock out for lunch (OUT for lunch) will be red if sooner than 12:00. Clock in after lunch (IN after lunch) will be red if more than 13:00. And go home time (OUT) will be red if less than 17:00 hours. Total working time (TOTAL) will be red if less than 8:00.
In the picture above, see blue arrow on cell I4 where VLOOKUP formula is
written and used to check an employee ID with number K0004, and the
formula finds the name Michael Shoemaker. Then there is SUMIF formula in cell J4 that add
up the total working hours for Michael Shoemaker and the result is 17:09.
In the picture above also can be seen yellow arrow that shows TOTAL calculation and it is using SUBTOTAL formula. The figure is value from TOTAL column and it will
change depending on the applied filter. Because the filter is
only showing K0004, then the value in cell L12 is 17:09 which is a total for Michael Shoemaker.
A simple example of VLOOKUP formula that is used to list a phone number as described earlier, are also available on this Excel software workbook on ‘simple’ sheet.
Competition Standings
For sports competition such as football, soccer, futsal, badminton. volleyball, basketball, and other competition. To create a list of participants rank of the competition please read on article "Learning Rank Formula For Sports Competition".
A simple example of VLOOKUP formula that is used to list a phone number as described earlier, are also available on this Excel software workbook on ‘simple’ sheet.
Competition Standings
For sports competition such as football, soccer, futsal, badminton. volleyball, basketball, and other competition. To create a list of participants rank of the competition please read on article "Learning Rank Formula For Sports Competition".
Are you looking for a high quality laptop or notebook? Various laptop brands like Apple Macbook, Samsung Chromebook, Dell Inspiron, ASUS Vivobook, Lenovo IdeaPad, HP Pavilion and much more, can be checked in "Best Seller Laptops".
Perfect piece of work you have done, this web site is really cool with great info . excel consulting
ReplyDelete