0
The following example will give you an idea on how to use VLOOKUP in Microsoft Excel. If you're working with an excel database and want it to be dynamic the VLOOKUP/HLOOKUP functions are very useful for you. The VLOOKUP function looks up value in columns while HLOOKUP does in rows.


 This example has two sheets "Price_List" and "Orders" for mobile brands.
*Note: The values given are not the actual market prices.


The "Price_List" sheet has three columns ID, Model and Price. This is where we'll be looking up the prices value for every orders made.


Now, on the orders tab we have 3 orders made. We'll add the VLOOKUP function in the Total Price formula. This formula "=VLOOKUP(B2,Price_List!B1:C7,2,FALSE)" will LOOKUP for the price of the unit brand in the Price_List Sheet.

=IF(C2<>"",VLOOKUP(B2,Price_List!B1:C7,2,FALSE) *C2,VLOOKUP(B2,Price_List!B1:C7,2,FALSE))
The formula above is a logical condition to calculate the Total Price when the quantity is entered.
The condition is:
If the quantity "C2" is blank then it will just lookup the unit price from the Price_List Sheet. And if the quantity is entered it will calculate by multiplying the unit price with the quantity value.

Post a Comment

 
Top