Excel VLOOKUP Function Tutorial

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.<br /><br /><div class="separator" style="clear: both; text-align: center;"></div><br /> This example has two sheets "Price_List" and "Orders" for mobile brands. <br />*Note: The values given are not the actual market prices.<br /><br /><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAaUwpgXip2lpnrGqQk5y0ovNoMC_OsNhD1N3c3amsg723hztYOOHeTgTvTuv0SxeR3qdUyhxHvi8xLawL9NIBCpXOrqx7i1Gq30nqCzEMPklCojFoJQ2BBXco4ObF4EoS8Dkv5URGCZ4/s1600/price+list.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="121" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAaUwpgXip2lpnrGqQk5y0ovNoMC_OsNhD1N3c3amsg723hztYOOHeTgTvTuv0SxeR3qdUyhxHvi8xLawL9NIBCpXOrqx7i1Gq30nqCzEMPklCojFoJQ2BBXco4ObF4EoS8Dkv5URGCZ4/s400/price+list.png" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDFgj48DCoj4vk6uQC3HoeX97ZjzoC2B7FPCTjuQIyRYjIWqJd5uKP9GrTBRB4s04JKmnGmamrd1ImnJ_ZwgZ0vEUCiST584NOgbgb0rb35RR0HLHa4k7JalfLingGSAUFMvWTNFOfVR0/s1600/lookup_formula.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /></a></div>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.<br /><br /><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDFgj48DCoj4vk6uQC3HoeX97ZjzoC2B7FPCTjuQIyRYjIWqJd5uKP9GrTBRB4s04JKmnGmamrd1ImnJ_ZwgZ0vEUCiST584NOgbgb0rb35RR0HLHa4k7JalfLingGSAUFMvWTNFOfVR0/s1600/lookup_formula.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDFgj48DCoj4vk6uQC3HoeX97ZjzoC2B7FPCTjuQIyRYjIWqJd5uKP9GrTBRB4s04JKmnGmamrd1ImnJ_ZwgZ0vEUCiST584NOgbgb0rb35RR0HLHa4k7JalfLingGSAUFMvWTNFOfVR0/s400/lookup_formula.png" width="400" /></a></div><br />Now, on the orders tab we have 3 orders made. We'll add the VLOOKUP function in the Total Price formula. This formula <span style="color: #6fa8dc;"><b>"=VLOOKUP(B2,Price_List!B1:C7,2,FALSE)"</b></span> will LOOKUP for the price of the unit brand in the Price_List Sheet.<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLC_lhA3IWlkoDDx4dpXMsJULiULifKw7jwP1-TRipusYU3EA0m7eNjE1DU-0Y29sCJ-eCpVhXjJa1lfXwU5_EV8LaRkZ-t3yXYTMYD8e8zVomi9Qe3fc3lkkpcBxiMEbMMVuNodjK9YQ/s1600/Total+the+price+with+the+quantity.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLC_lhA3IWlkoDDx4dpXMsJULiULifKw7jwP1-TRipusYU3EA0m7eNjE1DU-0Y29sCJ-eCpVhXjJa1lfXwU5_EV8LaRkZ-t3yXYTMYD8e8zVomi9Qe3fc3lkkpcBxiMEbMMVuNodjK9YQ/s1600/Total+the+price+with+the+quantity.png" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><span style="color: #9fc5e8; font-size: x-small;"><b>=IF(C2<>"",VLOOKUP(B2,Price_List!B1:C7,2,FALSE) *C2,VLOOKUP(B2,Price_List!B1:C7,2,FALSE))</b></span></td></tr></tbody></table><span style="font-size: small;">The formula above is a </span><i>logical condition</i> to calculate the <b>Total Price</b> when the quantity is entered.<br /><i>The condition is:</i><br /><blockquote class="tr_bq">If the quantity <b>"C2"</b> is blank then it will just <b>lookup the unit price from the Price_List Sheet</b>. And if the quantity is entered it will <b>calculate by multiplying the unit price with the quantity value</b>.</blockquote><br />

excel Excel VLookup Tutorial microsoft excel Excel VLOOKUP Function Tutorial

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 ...

Read more »