VLookup Function

Posted on | August 18, 2010 | No Comments

Function Description:

Vlookup stands for ‘Vertical’ lookup. This function searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

Syntax:

VLookup(lookup_value, table_array, column_index_number, range_lookup)
in simpler terms we can also say
VLookup(ItemtoFind, RangetoFind, ColumntoPickValueFrom, SortedorUnsorted)

Example:

A B C
1 Item Cost Discount
2 Brick 10 10%
3 Sand 25 15%
4 Cement 80 20%

Now we have the cost & discount table above and need to calculate the Total amount & the discount for the quantity listed in the below table. In this scenario, we can use ‘VLookup’ to calculate the Total amount & discount.

Lookup for Amount Calculation:

=G2*VLookup(F2,$A$1:$C$4,2,False)

This lookup function will look for the value ‘Brick’ within the range ‘A1:C4′ and returns the value ’10’, since we have provided column value ‘2’ in the formula. (I have used absolute reference to the range since the lookup range doesn’t change when we copy down the formula to other rows)

Lookup for Discount Calculation:

=H2-(H2*VLookup(F2,$A$1:$C$4,3,FALSE))

Final Result:

F G H I
1 Item Qty Amount Discounted Price
2 Brick 20 200 180
3 Sand 2 50 42.5
4 Cement 5 400 320

Comments

Leave a Reply