Dear All, What is V look-up? what is the purpose of this look-up? please guide me with some formats. Kindly note I would like to see the formulae also as I have no idea about this. Thanks & rgds,
From India, Gurgaon
Acknowledge(0)
Amend(0)
4From India, Gurgaon
Acknowledge(0)Amend(0)
VLOOKUP is generally used in Excel spreadsheets. In Excel, the VLOOKUP function searches for a value in the left-most column of the table_array and returns the value in the same row based on the index_number. You need to understand its function practically because by providing any format, you won't understand it. So, ask and learn this with someone who is an expert in Excel (MIS Executives know this function very well, so you can approach them).
Regards,
nm
From India, New Delhi
Acknowledge(0)Amend(0)
Regards,
nm
From India, New Delhi
Acknowledge(0)Amend(0)
The column numbers are not needed. They are part of the illustration.
col 1 col 2 col 3 col 4 col 5 col 6
Jan 10 20 30 40 50
Feb 80 90 100 110 120
Mar 97 69 45 51 77
Type a month to look for: Feb
Which column needs to be picked out: 4
The result is: 100
=VLOOKUP(G11,C6:H8,G12,FALSE)
What Does It Do?
This function scans down the row headings at the side of a table to find a specified item. When the item is found, it then scans across to pick a cell entry.
Syntax
=VLOOKUP(ItemToFind, RangeToLookIn, ColumnToPickFrom, SortedOrUnsorted)
- The ItemToFind is a single item specified by the user.
- The RangeToLookIn is the range of data with the row headings at the left-hand side.
- The ColumnToPickFrom is how far across the table the function should look to pick from.
- The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.
Formatting
No special formatting is needed.
Example 1
This table is used to find a value based on a specified name and month. The =VLOOKUP() is used to scan down to find the name. The problem arises when we need to scan across to find the month column. To solve the problem, the =MATCH() function is used. The =MATCH() looks through the list of names to find the month we require. It then calculates the position of the month in the list. Unfortunately, because the list of months is not as wide as the lookup range, the =MATCH() number is 1 less than we require, so an extra 1 is added to compensate. The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry. The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.
Jan Feb Mar
Bob 10 80 97
Eric 20 90 69
Alan 30 100 45
Carol 40 110 51
David 50 120 77
Type a name to look for: eric
Type a month to look for: mar
The result is: 69
=VLOOKUP(F56,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)
Example 2
This example shows how the =VLOOKUP() is used to pick the cost of a spare part for different makes of cars. The =VLOOKUP() scans down row headings in column F for the spare part entered in column C. When the make is found, the =VLOOKUP() then scans across to find the price, using the result of the =MATCH() function to find the position of the make of car. The functions use the absolute ranges indicated by the dollar symbol. This ensures that when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do not change.
Maker Spare Cost Lookup Table
Vauxhall Ignition £50 Vauxhall Ford VW
VW GearBox £600 GearBox 500 450 600
Ford Engine £1,200 Engine 1000 1200 800
VW Steering £275 Steering 250 350 275
Ford Ignition £70 Ignition 50 70 45
Ford CYHead £290 CYHead 300 290 310
Vauxhall GearBox £500
Ford Engine £1,200
=VLOOKUP(C81,F75:I79,MATCH(B81,G74:I74,0)+1,FALSE)
Example 3
In the following example, a builder's merchant is offering discounts on large orders. The Unit Cost Table holds the cost of 1 unit of Brick, Wood, and Glass. The Discount Table holds the various discounts for different quantities of each product. The Orders Table is used to enter the orders and calculate the Total. All the calculations take place in the Orders Table. The name of the Item is typed in column C of the Orders Table. The Unit Cost of the item is then looked up in the Unit Cost Table. The FALSE option has been used at the end of the function to indicate that the product names down the side of the Unit Cost Table are not sorted. Using the FALSE option forces the function to search for an exact match. If a match is not found, the function will produce an error.
=VLOOKUP(C126,C114:D116,2,FALSE)
The discount is then looked up in the Discount Table. If the Quantity Ordered matches a value at the side of the Discount Table, the =VLOOKUP will look across to find the correct discount. The TRUE option has been used at the end of the function to indicate that the values down the side of the Discount Table are sorted. Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does not match a value at the side of the Discount Table, the next lowest value is used. Trying to match an order of 125 will drop down to 100, and the discount from the 100 row is used.
=VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1, TRUE)
Discount Table
Unit Cost Table Brick Wood Glass
Brick £2 1 0% 0% 0%
Wood £1 100 6% 3% 12%
Glass £3 300 8% 5% 15%
Orders Table
Item Units Unit Cost Discount Total
Brick 100 £2 6% £188
Wood 200 £1 3% £194
Glass 150 £3 12% £396
Brick 225 £2 6% £423
Wood 50 £1 0% £50
Glass 500 £3 15% £1,275
Formula for:
Unit Cost =VLOOKUP(C126,C114:D116,2,FALSE)
Discount =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1, TRUE)
Total = (D126*E126)-(D126*E126*F126)
From India, Delhi
Acknowledge(0)Amend(0)
col 1 col 2 col 3 col 4 col 5 col 6
Jan 10 20 30 40 50
Feb 80 90 100 110 120
Mar 97 69 45 51 77
Type a month to look for: Feb
Which column needs to be picked out: 4
The result is: 100
=VLOOKUP(G11,C6:H8,G12,FALSE)
What Does It Do?
This function scans down the row headings at the side of a table to find a specified item. When the item is found, it then scans across to pick a cell entry.
Syntax
=VLOOKUP(ItemToFind, RangeToLookIn, ColumnToPickFrom, SortedOrUnsorted)
- The ItemToFind is a single item specified by the user.
- The RangeToLookIn is the range of data with the row headings at the left-hand side.
- The ColumnToPickFrom is how far across the table the function should look to pick from.
- The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.
Formatting
No special formatting is needed.
Example 1
This table is used to find a value based on a specified name and month. The =VLOOKUP() is used to scan down to find the name. The problem arises when we need to scan across to find the month column. To solve the problem, the =MATCH() function is used. The =MATCH() looks through the list of names to find the month we require. It then calculates the position of the month in the list. Unfortunately, because the list of months is not as wide as the lookup range, the =MATCH() number is 1 less than we require, so an extra 1 is added to compensate. The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry. The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.
Jan Feb Mar
Bob 10 80 97
Eric 20 90 69
Alan 30 100 45
Carol 40 110 51
David 50 120 77
Type a name to look for: eric
Type a month to look for: mar
The result is: 69
=VLOOKUP(F56,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)
Example 2
This example shows how the =VLOOKUP() is used to pick the cost of a spare part for different makes of cars. The =VLOOKUP() scans down row headings in column F for the spare part entered in column C. When the make is found, the =VLOOKUP() then scans across to find the price, using the result of the =MATCH() function to find the position of the make of car. The functions use the absolute ranges indicated by the dollar symbol. This ensures that when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do not change.
Maker Spare Cost Lookup Table
Vauxhall Ignition £50 Vauxhall Ford VW
VW GearBox £600 GearBox 500 450 600
Ford Engine £1,200 Engine 1000 1200 800
VW Steering £275 Steering 250 350 275
Ford Ignition £70 Ignition 50 70 45
Ford CYHead £290 CYHead 300 290 310
Vauxhall GearBox £500
Ford Engine £1,200
=VLOOKUP(C81,F75:I79,MATCH(B81,G74:I74,0)+1,FALSE)
Example 3
In the following example, a builder's merchant is offering discounts on large orders. The Unit Cost Table holds the cost of 1 unit of Brick, Wood, and Glass. The Discount Table holds the various discounts for different quantities of each product. The Orders Table is used to enter the orders and calculate the Total. All the calculations take place in the Orders Table. The name of the Item is typed in column C of the Orders Table. The Unit Cost of the item is then looked up in the Unit Cost Table. The FALSE option has been used at the end of the function to indicate that the product names down the side of the Unit Cost Table are not sorted. Using the FALSE option forces the function to search for an exact match. If a match is not found, the function will produce an error.
=VLOOKUP(C126,C114:D116,2,FALSE)
The discount is then looked up in the Discount Table. If the Quantity Ordered matches a value at the side of the Discount Table, the =VLOOKUP will look across to find the correct discount. The TRUE option has been used at the end of the function to indicate that the values down the side of the Discount Table are sorted. Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does not match a value at the side of the Discount Table, the next lowest value is used. Trying to match an order of 125 will drop down to 100, and the discount from the 100 row is used.
=VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1, TRUE)
Discount Table
Unit Cost Table Brick Wood Glass
Brick £2 1 0% 0% 0%
Wood £1 100 6% 3% 12%
Glass £3 300 8% 5% 15%
Orders Table
Item Units Unit Cost Discount Total
Brick 100 £2 6% £188
Wood 200 £1 3% £194
Glass 150 £3 12% £396
Brick 225 £2 6% £423
Wood 50 £1 0% £50
Glass 500 £3 15% £1,275
Formula for:
Unit Cost =VLOOKUP(C126,C114:D116,2,FALSE)
Discount =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1, TRUE)
Total = (D126*E126)-(D126*E126*F126)
From India, Delhi
Acknowledge(0)Amend(0)
Find the brief Excel help in this attachment . It will make you clear all the functions of EXCEL. Cherookee
From India, Ranchi
Acknowledge(0)Amend(0)
From India, Ranchi
Acknowledge(0)Amend(0)
Using VLOOKUP for Data Matching
VLOOKUP is used to match data with a common name. For example, if you have employee IDs ranging from 1 to 10,000 and you have given advances to employees numbered 298, 501, 222, 781, 346, etc., you cannot manually input advance data in front of all employees by searching one by one. Using the VLOOKUP formula, it will match advances against the same ID.
For instance, if you have one Excel sheet of employees with details such as ID, name, designation, and other information, and another Excel sheet of advances, you can copy advances onto the first sheet against the same ID using VLOOKUP from the sheet containing the advance information.
From India, Mumbai
Acknowledge(0)Amend(0)
VLOOKUP is used to match data with a common name. For example, if you have employee IDs ranging from 1 to 10,000 and you have given advances to employees numbered 298, 501, 222, 781, 346, etc., you cannot manually input advance data in front of all employees by searching one by one. Using the VLOOKUP formula, it will match advances against the same ID.
For instance, if you have one Excel sheet of employees with details such as ID, name, designation, and other information, and another Excel sheet of advances, you can copy advances onto the first sheet against the same ID using VLOOKUP from the sheet containing the advance information.
From India, Mumbai
Acknowledge(0)Amend(0)
You can try here. Herewith, I have attached two sheets: one is the employee sheet and the other is the employee code. You can see the VLOOKUP formula in the employee sheet in the column code.
Have a look, it may help you. If you require more help, then email me at [Email Removed For Privacy Reasons].
Regards,
Dhruvin
From India, Ahmadabad
Acknowledge(0)Amend(0)
Have a look, it may help you. If you require more help, then email me at [Email Removed For Privacy Reasons].
Regards,
Dhruvin
From India, Ahmadabad
Acknowledge(0)Amend(0)
Orders Table
Item | Units | Unit Cost | Discount | Total
---|---|---|---|---
Brick | 100 | £2 | 6% | £188
Wood | 200 | £1 | 3% | £194
Glass | 150 | £3 | 12% | £396
Brick | 225 | £2 | 6% | £423
Wood | 50 | £1 | 0% | £50
Glass | 500 | £3 | 15% | £1,275
Formula for Calculations:
- **Unit Cost:** =VLOOKUP(C126,C114116,2,FALSE)
- **Discount:** =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1, TRUE)
- **Total:** =(D126*E126)-(D126*E126*F126)
More information can be found at https://www.citehr.com/298991-vlook-...#ixzz16TGOpeM4
From India, Mumbai
Acknowledge(0)Amend(0)
Item | Units | Unit Cost | Discount | Total
---|---|---|---|---
Brick | 100 | £2 | 6% | £188
Wood | 200 | £1 | 3% | £194
Glass | 150 | £3 | 12% | £396
Brick | 225 | £2 | 6% | £423
Wood | 50 | £1 | 0% | £50
Glass | 500 | £3 | 15% | £1,275
Formula for Calculations:
- **Unit Cost:** =VLOOKUP(C126,C114116,2,FALSE)
- **Discount:** =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1, TRUE)
- **Total:** =(D126*E126)-(D126*E126*F126)
More information can be found at https://www.citehr.com/298991-vlook-...#ixzz16TGOpeM4
From India, Mumbai
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Excel Formulas.xls
Excel Formulas.ppt