How do I use Excel's VLOOKUP function?

I want to use Microsoft Excel's VLOOKUP function to find data in my spreadsheet. I've tried looking at online guides, but I'm still confused as to how the function actually works.

How do I use the VLOOKUP function in Microsoft Excel?

Solution 1:

If you have some Data like in the example :

A        B                   C
Number  Part               Price
A001    Water Pump          68.39
A002    Alternator         380.73
A003    Air Filter          15.4
A004    Wheel Bearing       35.16
A005    Muffler            160.23
A006    Oil pan            101.89
A007    Brake pads          65.99
A008    Brake rotors        85.73
A009    Headlight           35.19
A010    Brake cable         15.49

If you need to write the number in another place and you need to retrieve the Part or the Price you use Vlookup.

=VLOOKUP(lookup value, lookup array(range containing the lookup value and the result value), the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match).
If you want the price of A005 in D1 you write in E1:

D       E  
A005    =Vlookup(D1,$A$11:$C$11,3,False)

Lookup Array is $A$11:$C$11
Lookup Value is D1
3 is the third column (price column)
False to return the exact match

Another use of Vlookup
In case you need an approximate match not exact match like the following:

A             B
Quantity    Price
0           10
100          9.5
500          9
1000         8.5

• the price is 10 if the quantity is less than 100
• the price is 9.5 for quantity between 100 and 500
• the price is 9 for quantity between 500 and 1000
• the price is 8.5 if quantity is >=1000

Column A should be sorted ascending (A and B selected and sort by column A ascending)

 D      E
300     9.5 =VLOOKUP(D1,$A$2:$B$5,2,TRUE)
200     9.5
750     9
1003    8.5

$A$2:$B$5 is the first column A and B Quantity and Price
$ for fixed reference
2 to return the second column Price
True for approximate match