No Tags Found!

kavitabhatia30
4

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
nm2004hr
Kavita,
Vlookup generally we are using in Excel sheet. In Excel, the VLookup function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.
You need to understand its funtion practically coz by providing any format u won't be understand. So, ask and learn this with someone who is expert in Excel (MIS Executive knows this function very well, so, u can approach them).
Rgds
nm

From India, New Delhi
shyam.sundar
10

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 and 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 builders merchant is offering discount 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
Cherookee
32

Find the brief Excel help in this attachment . It will make you clear all the functions of EXCEL. Cherookee
From India, Ranchi
Attached Files (Download Requires Membership)
File Type: xls Excel Help.xls (1.19 MB, 1355 views)

sgenius
vlookup is used to match the data with common name.
eg-you have employee id from 1 to 10000. you gave advance to employee no. 298, 501, 222, 781, 346...... now you cannot go and put data of advance in front of all employee by searching one by one. so using vlookup formulae it will copy advances against same id.
eg- you have one excel sheet of employees with respect to id,name,designation, and other details.
another excel sheet of advances.
you can copy advances in 1st sheet against same id using vlookup from advance given sheet.

From India, Mumbai
Dhruvin Sureliya
22

you can try here
herewith i have attach two sheet one is employee sheet and one is employee code ......you can she the vlook formula in employee sheet in coloumn code.
hav a look may be help you if you require more hepl then mail me on
Regards
Dhruvin

From India, Ahmadabad
Attached Files (Download Requires Membership)
File Type: xls employee data.xls (26.0 KB, 1344 views)
File Type: xls employee sheet.xls (56.0 KB, 1014 views)

helpaady
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,C114116,2,FALSE)
Discount =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1, TRUE)
Total =(D126*E126)-(D126*E126*F126)
more at https://www.citehr.com/298991-vlook-...#ixzz16TGOpeM4

From India, Mumbai
imrankhan.shah
Dear Freinds, Find the Attachment file.
From India, Hyderabad
Attached Files (Download Requires Membership)
File Type: xls EXCEL_Formulae.xls (2.47 MB, 774 views)

anil.arora
664

Hi Cherookee/Imran/Dhruvin/Shyam,
The attachments and details you have posted here is really helpful and wanna offer my thanks to all of you for the same..keep posting the material like this, this wud dfnlty help the peoples...

From India, Gurgaon
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.





Contact Us Privacy Policy Disclaimer Terms Of Service

All rights reserved @ 2024 CiteHR ®

All Copyright And Trademarks in Posts Held By Respective Owners.