{"id":4847,"date":"2025-11-06T22:43:08","date_gmt":"2025-11-06T21:43:08","guid":{"rendered":"https:\/\/sandshine.eu\/?p=4847"},"modified":"2025-12-27T12:30:25","modified_gmt":"2025-12-27T11:30:25","slug":"vlookup-function-in-excel-complete-tutorial-with-examples","status":"publish","type":"post","link":"https:\/\/sandshine.eu\/index.php\/2025\/11\/06\/vlookup-function-in-excel-complete-tutorial-with-examples\/","title":{"rendered":"VLOOKUP Function in Excel: Complete Tutorial with Examples"},"content":{"rendered":"\n<p>Need to find information in a large table? VLOOKUP searches for a value in the first column and returns data from any column to the right. It&#8217;s one of Excel&#8217;s most-used functions for a reason.<\/p>\n\n\n\n<p>To download the exercise file for this function, click <a href=\"https:\/\/sandshine.eu\/wp-content\/uploads\/2025\/12\/VLOOKUP.xlsx\">here<\/a>.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">What Makes VLOOKUP Useful<\/h5>\n\n\n\n<p>VLOOKUP saves you from scrolling through endless rows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Quick lookups<\/strong> &#8211; Find data in seconds instead of minutes<\/li>\n\n\n\n<li><strong>Automatic updates<\/strong> &#8211; Results change when your source data changes<\/li>\n\n\n\n<li><strong>Works with large datasets<\/strong> &#8211; Handle thousands of rows without slowing down<\/li>\n\n\n\n<li><strong>Approximate matching<\/strong> &#8211; Find the closest match for price tiers or ranges<\/li>\n\n\n\n<li><strong>Cross-sheet lookups<\/strong> &#8211; Pull data from other worksheets in your workbook<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">The Syntax<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>=VLOOKUP(lookup_value, table_array, col_index_num, &#91;range_lookup])<\/code><\/pre>\n\n\n\n<p>The lookup_value is what you&#8217;re searching for. Table_array is where you&#8217;re searching. Col_index_num tells Excel which column to return.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-bc7582b\" data-block-id=\"bc7582b\"><style>.stk-bc7582b {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 1: Basic Product Lookup<\/h5>\n\n\n\n<p>Find the price for product P002:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product Code<\/th><th>Product Name<\/th><th>Price<\/th><th>Stock<\/th><\/tr><\/thead><tbody><tr><td>P001<\/td><td>Wireless Mouse<\/td><td>$29.99<\/td><td>45<\/td><\/tr><tr><td>P002<\/td><td>USB Keyboard<\/td><td>$45.50<\/td><td>23<\/td><\/tr><tr><td>P003<\/td><td>Monitor Stand<\/td><td>$67.25<\/td><td>12<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=VLOOKUP(\"P002\", A2:D4, 3, FALSE)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: $45.50<\/p>\n\n\n\n<p>Excel searches column A for &#8220;P002&#8221;, then returns the value from column 3 (the Price column). FALSE means exact match only.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-aec17d2\" data-block-id=\"aec17d2\"><style>.stk-aec17d2 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 2: Using Cell References<\/h5>\n\n\n\n<p>Look up the product code entered in cell F1:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product Code<\/th><th>Product Name<\/th><th>Price<\/th><\/tr><\/thead><tbody><tr><td>P001<\/td><td>Wireless Mouse<\/td><td>$29.99<\/td><\/tr><tr><td>P002<\/td><td>USB Keyboard<\/td><td>$45.50<\/td><\/tr><tr><td>P003<\/td><td>Monitor Stand<\/td><td>$67.25<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=VLOOKUP(F1, A2:C4, 2, FALSE)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: Returns the product name for whatever code is in F1<\/p>\n\n\n\n<p>This makes your lookup dynamic. Change F1 and the result updates automatically.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-d8bb5d7\" data-block-id=\"d8bb5d7\"><style>.stk-d8bb5d7 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 3: Commission Tiers with Approximate Match<\/h5>\n\n\n\n<p>Find the commission rate for $32,000 in sales:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Sales Threshold<\/th><th>Commission Rate<\/th><\/tr><\/thead><tbody><tr><td>$0<\/td><td>5%<\/td><\/tr><tr><td>$10,000<\/td><td>7%<\/td><\/tr><tr><td>$25,000<\/td><td>10%<\/td><\/tr><tr><td>$50,000<\/td><td>12%<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=VLOOKUP(32000, A2:B5, 2, TRUE)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 10%<\/p>\n\n\n\n<p>TRUE tells Excel to find the closest match without going over. The first column must be sorted ascending for this to work.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-ae08bd9\" data-block-id=\"ae08bd9\"><style>.stk-ae08bd9 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 4: Looking Up from Another Sheet<\/h5>\n\n\n\n<p>Pull employee data from a sheet named &#8220;ProductList&#8221;:<\/p>\n\n\n\n<p><strong>Formula<\/strong>: <code>=VLOOKUP(\"P002\", ProductList!A:D, 3, FALSE)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: Returns data from column 3 of the ProductList sheet<\/p>\n\n\n\n<p>Use SheetName! before the range. This keeps your data organized across multiple sheets.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-3e11522\" data-block-id=\"3e11522\"><style>.stk-3e11522 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 5: Handling Errors with IFERROR<\/h5>\n\n\n\n<p>What if the lookup value doesn&#8217;t exist?<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product Code<\/th><th>Product Name<\/th><th>Price<\/th><\/tr><\/thead><tbody><tr><td>P001<\/td><td>Wireless Mouse<\/td><td>$29.99<\/td><\/tr><tr><td>P002<\/td><td>USB Keyboard<\/td><td>$45.50<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=IFERROR(VLOOKUP(\"P999\", A2:C3, 3, FALSE), \"Not found\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: &#8220;Not found&#8221;<\/p>\n\n\n\n<p>IFERROR wraps your VLOOKUP and shows a custom message instead of #N\/A errors.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-a722a22\" data-block-id=\"a722a22\"><style>.stk-a722a22 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 6: Tax Bracket Calculation<\/h5>\n\n\n\n<p>Find the tax rate for income of $75,000:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Income Level<\/th><th>Tax Rate<\/th><\/tr><\/thead><tbody><tr><td>$0<\/td><td>10%<\/td><\/tr><tr><td>$10,000<\/td><td>12%<\/td><\/tr><tr><td>$40,000<\/td><td>22%<\/td><\/tr><tr><td>$85,000<\/td><td>24%<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=VLOOKUP(75000, A2:B5, 2, TRUE)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 22%<\/p>\n\n\n\n<p>Approximate match finds $40,000 (the highest value that doesn&#8217;t exceed $75,000) and returns its tax rate.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-ee44cc2\" data-block-id=\"ee44cc2\"><style>.stk-ee44cc2 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Range Lookup Options<\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Value<\/th><th>Behavior<\/th><th>When to Use<\/th><\/tr><\/thead><tbody><tr><td>FALSE or 0<\/td><td>Exact match only<\/td><td>Product codes, employee IDs, specific items<\/td><\/tr><tr><td>TRUE or 1<\/td><td>Approximate match<\/td><td>Price tiers, tax brackets, grade ranges<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Common Column Index Numbers<\/h5>\n\n\n\n<p>If your table has 5 columns and you want:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Column 1: Use 1<\/li>\n\n\n\n<li>Column 2: Use 2<\/li>\n\n\n\n<li>Column 3: Use 3<\/li>\n\n\n\n<li>And so on&#8230;<\/li>\n<\/ul>\n\n\n\n<p>Count from the first column of your table_array, not from column A of the spreadsheet.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Important Notes<\/h5>\n\n\n\n<ul class=\"wp-block-list\">\n<li>VLOOKUP only searches the first column of your range<\/li>\n\n\n\n<li>It can&#8217;t look to the left (only returns columns to the right)<\/li>\n\n\n\n<li>The first column must be sorted ascending when using TRUE<\/li>\n\n\n\n<li>Column index must be a positive number<\/li>\n\n\n\n<li>Table_array must include both the lookup column and return column<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Start Using It<\/h5>\n\n\n\n<p>Pick a table in your spreadsheet. Choose something to look up. Write a VLOOKUP formula. Try it with FALSE first, then experiment with TRUE for approximate matches.<\/p>\n\n\n\n<p>VLOOKUP becomes second nature after a few tries.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p><em>Questions about VLOOKUP? Need help with a specific lookup scenario? Let&#8217;s connect.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>VLOOKUP searches tables in seconds, updates automatically, and works with thousands of rows. Find data fast without endless scrolling.<\/p>\n","protected":false},"author":1,"featured_media":4848,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[35,36,37],"tags":[1065,1073,1019,1026,1057,1012,1066,1052,1054,1061,1063,1064,1060,1056,1059,1053,1055,1068,1071,1072,1058,1069,1062,1070,1067],"class_list":["post-4847","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-applications","category-excel","category-learning","tag-advanced-vlookup-techniques","tag-excel-365-vlookup","tag-excel-data-lookup","tag-excel-formulas-tutorial","tag-excel-lookup-tutorial","tag-excel-tutorial-2025","tag-excel-vlookup-guide","tag-excel-vlookup-tutorial","tag-how-to-use-vlookup","tag-learn-vlookup-excel","tag-vlookup-approximate-match","tag-vlookup-column-index","tag-vlookup-exact-match","tag-vlookup-examples","tag-vlookup-for-beginners","tag-vlookup-formula-explained","tag-vlookup-function-in-excel","tag-vlookup-multiple-sheets","tag-vlookup-not-working","tag-vlookup-range-lookup","tag-vlookup-step-by-step","tag-vlookup-syntax","tag-vlookup-tips","tag-vlookup-troubleshooting","tag-vlookup-with-iferror"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"_links":{"self":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4847","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/comments?post=4847"}],"version-history":[{"count":3,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4847\/revisions"}],"predecessor-version":[{"id":5018,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4847\/revisions\/5018"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media\/4848"}],"wp:attachment":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media?parent=4847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/categories?post=4847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/tags?post=4847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}