{"id":3407,"date":"2017-09-12T10:48:31","date_gmt":"2017-09-12T16:48:31","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3407"},"modified":"2021-11-01T10:05:00","modified_gmt":"2021-11-01T16:05:00","slug":"vlookup-and-index-match-examples","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-formulas\/vlookup-and-index-match-examples.html","title":{"rendered":"VLOOKUP and INDEX-MATCH Examples in Excel"},"content":{"rendered":"<div class=\"feature-image\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/feature-vlookup-index-match.png\" alt=\"VLOOKUP and INDEX-MATCH in Excel (Power Functions)\" style=\"max-width:100%;margin:1em auto;\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/feature-vlookup-index-match.png\"><meta itemprop=\"width\" content=\"1200\"><meta itemprop=\"height\" content=\"628\"><\/div>\n<p>VLOOKUP and INDEX-MATCH formulas are among the most powerful functions in Excel. Lookup formulas come in handy whenever you want to have Excel automatically return the price, product ID, address, or some other associated value from a table based on some lookup value. The <b>VLOOKUP<\/b> function can be used when the lookup value is in the left column of your table or when you want to return the last value in a column. The <b>INDEX and MATCH<\/b> functions can be used in combination to do the same thing, but provide greater flexibility without some of the limitations of VLOOKUP. I'll also mention LOOKUP and CHOOSE and EXACT and ISBLANK and ISNUMBER and ISTEXT and ... &#128578;, but this article is mainly about VLOOKUP and INDEX-MATCH.<\/p>\n<p>To see these examples in action, download the Excel file below.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/LookupFormulas.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'LookupFormulas.xlsx');\" class=\"bigbtn\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (LookupFormulas.xlsx)<\/p>\n<p><strong>Do you have a VLOOKUP or INDEX-MATCH challenge you need to solve?<\/strong> If you can't figure it out after reading this article, go ahead and ask your question by commenting below.<\/p>\n<div class=\"contents\">\n<p>This Article (bookmarks):<\/p>\n<ul>\n<li><a href=\"#simple-examples\"><b>Simple<\/b> VLOOKUP and INDEX-MATCH Examples<\/a><\/li>\n<li><a href=\"#wildcard\"><b>Wildcard<\/b> Characters for Partial Match Lookups<\/a><\/li>\n<li><a href=\"#approximate\"><b>Approximate<\/b> Match Lookups (for Grades, Discounts, Taxes, etc.)<\/a><\/li>\n<li><a href=\"#2d-lookups\"><b>2D<\/b> Lookups Using VLOOKUP-MATCH and INDEX-MATCH-MATCH<\/a><\/li>\n<li><a href=\"#3d-lookups\"><b>3D<\/b> Lookups Using INDEX-MATCH and VLOOKUP<\/a><\/li>\n<li><a href=\"#exact\"><b>Case-Sensitive<\/b> EXACT Lookup Using INDEX-MATCH<\/a><\/li>\n<li><a href=\"#multiple-criteria\"><b>Multiple-Criteria<\/b> Exact Lookups Using VLOOKUP and INDEX-MATCH<\/a><\/li>\n<li><a href=\"#non-exact\">Lookups with <b>Multiple Non-Exact Criteria<\/b> Using INDEX-MATCH<\/a><\/li>\n<li><a href=\"#last-numeric-value\">Return the <b>Last Numeric Value<\/b> in a Column<\/a><\/li>\n<li><a href=\"#last-text-value\">Return the <b>Last Text Value<\/b> in a Column<\/a><\/li>\n<li><a href=\"#last-non-blank-value\">Return the <b>Last Non-BLANK Value<\/b> in a Range<\/a><\/li>\n<li><a href=\"#last-non-empty-value\">Return the <b>Last Non-Empty Value<\/b> in a Range<\/a><\/li>\n<li><a href=\"#lookup-nth-match\">Lookup based on the <b>Nth Match<\/b><\/a><\/li>\n<\/ul>\n<\/div>\n<h2 id=\"simple-examples\">1) Simple VLOOKUP and INDEX-MATCH Examples<\/h2>\n<p><\/p>\n<div itemprop=\"video\" itemscope itemtype=\"http:\/\/schema.org\/VideoObject\">\n<iframe title=\"Watch the Video\" width=\"720\" height=\"405\" src=\"https:\/\/www.youtube.com\/embed\/AUiOXE7fOYE?rel=0\" frameborder=\"0\" allowfullscreen><\/iframe><meta itemprop=\"name\" content=\"How to Use VLOOKUP and INDEX MATCH in Excel\"><meta itemprop=\"description\" content=\"Learn how to use the VLOOKUP and INDEX MATCH functions in Excel to do simple table lookups. Demonstrates how to use VLOOKUP in a purchase order template to get vendor and product information.\"><meta itemprop=\"thumbnailURL\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/how-to-use-vlookup-and-index-match-in-excel-720x405.png\"><meta itemprop=\"embedURL\" content=\"https:\/\/www.youtube.com\/embed\/AUiOXE7fOYE?rel=0\"><meta itemprop=\"uploadDate\" content=\"2017-10-26\">\n<\/div>\n<h3>VLOOKUP Example<\/h3>\n<p>First, here is an example of the <b>VLOOKUP<\/b> function using a simple Price List Table. We're looking for the text \"a_003\" within the Item ID column and wanting to return the corresponding value from the Price column.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/vlookup-in-excel-formula-example.png\" alt=\"Excel VLOOKUP Function Example\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula\">=<span class=\"function\">VLOOKUP<\/span>(<span class=\"arg\">lookup_value<\/span>,<span class=\"arg\">table_array<\/span>,<span class=\"arg\">col_index_num<\/span>,FALSE)<\/pre>\n<p><b>How it works<\/b>: The <i>table_array<\/i> argument is a range that must have the lookup column on the left. The price column is the 3rd column in the highlighted range, so that is why the <i>col_index_num<\/i> argument is 3 in our example. We use FALSE for the final argument because we want the VLOOKUP function to do an exact match.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTES<\/span> Actually, this lookup is not truly \"exact\" because both VLOOKUP and MATCH are <em>not<\/em> case-sensitive, but the syntax tooltip in Excel calls the option an \"exact match\" so we'll just accept that and explain how to do a case-sensitive match later.<\/p>\n<p>If you later insert a column in the middle of your table_array, the Price column might not be column 3 any more. To prevent your VLOOKUP formula from breaking, in place of the 3 in the example, you can use (COLUMN($E$30)-COLUMN($C$30)+1).<\/p>\n<p>The default for VLOOKUP is not an exact match, so don't forget to include FALSE as the 4th argument if you want an exact match.<\/p>\n<\/div>\n<h3>INDEX-MATCH Example<\/h3>\n<p>Next, you'll see that the <b>INDEX-MATCH<\/b> formula is just as simple:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/index-match-in-excel-formula-example.png\" alt=\"Excel INDEX-MATCH Function Example\" width=\"600\" height=\"236\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula\">=<span class=\"function\">INDEX<\/span>(<span class=\"arg\">result_range<\/span>,<span class=\"function\">MATCH<\/span>(<span class=\"arg\">lookup_value<\/span>,<span class=\"arg\">lookup_range<\/span>,0))\r\n<\/pre>\n<p><b>How it works<\/b>: The MATCH function returns the <b>position number 3<\/b> because \"a_003\" matches the 3rd row in the Item ID range. Next, INDEX(<i>result_range<\/i>,3) returns the 3rd value in the price list range.<\/p>\n<p>The INDEX-MATCH formula is an example of a simple <b>nested function<\/b> where we use the result from the MATCH function as one of the arguments for the INDEX function. The example below shows this being done in two separate steps.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/how-index-match-formula-works.png\" alt=\"How the INDEX-MATCH Function Works\" width=\"677\" height=\"193\" class=\"border imgshadow\"><\/p>\n<h3>Syntax and Notes for MATCH and INDEX<\/h3>\n<h4><b>MATCH<\/b> returns the <b>position number<\/b> of a matched value within the lookup range.<\/h4>\n<pre class=\"XLformula light\">=<span class=\"function\">MATCH<\/span>(<span class=\"arg\">lookup_value<\/span>,<span class=\"arg\">lookup_range<\/span>,<span class=\"arg\">match_type<\/span>)<\/pre>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTES<\/span> When using MATCH, the <i>lookup_range<\/i> can be a row or column, but if <i>lookup_range<\/i> is more than one row or column, MATCH will return an error.<\/p>\n<p>MATCH returns the #N\/A error when it does not find a match.<\/p>\n<p>The <i>match_type<\/i> is optional, but the default is not 0. So, it is is best to always specify the match type.<\/p>\n<\/div>\n<h4><b>INDEX<\/b> returns a value from an array based on a row and column number.<\/h4>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>(<span class=\"arg\">array<\/span>,<span class=\"arg\">row_number<\/span>,[<span class=\"arg\">column_number<\/span>],[<span class=\"arg\">area_number<\/span>])\r\n<\/pre>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTES<\/span> You don't need to include the optional <i>column_number<\/i> if the array is a single column.<\/p>\n<p>The optional <i>area_number<\/i> argument is only used for 3D arrays.<\/p>\n<p>If your array is a row, don't use the shortcut =INDEX(<i>array<\/i>,<i>column_number<\/i>) because that may not be compatible with other spreadsheet software. Use =INDEX(<i>array<\/i>,1,<i>column_number<\/i>) instead.<\/p>\n<\/div>\n<div class=\"template-feature\">\n<p><a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/purchase-order-price-list.html\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTemplates\/Images\/purchase-order-with-price-list_180.png\" alt=\"Purchase Order with Price List Lookup\" width=\"139\" height=\"180\" style=\"float:left;margin-right:1em;\" class=\"imgshadow\"><b>Purchase Order with Price List<\/b><\/a><\/p>\n<p>See INDEX-MATCH in action. In this template, you choose an Item Description from a <a href=\"\/blog\/help\/excel-help\/create-a-drop-down-list-in-excel.html\">drop-down list<\/a>, then lookup formulas display the Item # and the Unit Price for that item.<\/p>\n<div class=\"clear\"><\/div>\n<\/div>\n<h2 id=\"wildcard\">2) Use Wildcard Characters (?, *) for Partial Matches with VLOOKUP and INDEX-MATCH<\/h2>\n<p>Wildcard characters can be used within the <i>lookup_value<\/i> for both VLOOKUP and MATCH formulas when the lookup is text and you are doing an exact match.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/wildcard-characters-in-vlookup-and-index-match.png\" alt=\"Using Wildcard Characters in VLOOKUP and INDEX-MATCH\" width=\"652\" height=\"170\" class=\"border imgshadow\"><\/p>\n<p><b>* (asterisk)<\/b> matches any number of characters. For example, use \"*200\" to find the first value ending in 200.<\/p>\n<p><b>? (question mark)<\/b> matches any single character. For example, use \"A?C*\" to find the first value where \"A\" is the first character and \"C\" is the 3rd character. The second character can be anything, but only a single character.<\/p>\n<p><b>~ (tilde)<\/b> is used in front of a wildcard character to treat it as a literal character. In the 3rd example, we use \"*~?*\" to find the first occurrence of a product that contains an actual question mark.<\/p>\n<h2 id=\"approximate\">3) Return Approximate Matches Using VLOOKUP and INDEX-MATCH<\/h2>\n<p>The following examples show how to use VLOOKUP and INDEX-MATCH to return approximate matches with numerical lookup data. <b>Important<\/b>: When using an \"Approximate Match\" with VLOOKUP (where the 4th argument = TRUE) and a \"Less than\" match with MATCH (where the 3rd argument = 1), <b>the lookup range needs to be sorted in ascending order<\/b>.<\/p>\n<p>These formulas look for the largest value that is less than or equal to the lookup value.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">VLOOKUP<\/span>(<span class=\"arg\">lookup_value<\/span>,<span class=\"arg\">table_array<\/span>,<span class=\"arg\">col_index_num<\/span>,<b>TRUE<\/b>)<\/pre>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>(<span class=\"arg\">result_range<\/span>,<span class=\"function\">MATCH<\/span>(<span class=\"arg\">lookup_value<\/span>,<span class=\"arg\">lookup_range<\/span>,<b>1<\/b>))\r\n<\/pre>\n<h3>Example 1: Return a Grade based on Percent<\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/vlookup-example-lookup-grades.png\" alt=\"Lookup Grades Using VLOOKUP\" width=\"530\" height=\"235\" class=\"border imgshadow\"><\/p>\n<p>&#9658; See this in action: Download the <a href=\"\/ExcelTemplates\/gradebook.html\"><b>Grade book Template<\/b><\/a><\/p>\n<h3>Example 2: Return a Discount Rate based on Quantity<\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/index-match-example-discount-lookup.png\" alt=\"Discount Rate Lookup Using INDEX-MATCH\" width=\"530\" height=\"212\" class=\"border imgshadow\"><\/p>\n<h3>Example 3: Return a Tax Rate based on Income<\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/vlookup-example-lookup-tax-bracket.png\" alt=\"Tax Rate Lookup Using VLOOKUP\" class=\"border imgshadow\"><\/p>\n<p>&#9658; See this in action: Download the <a href=\"\/Calculators\/paycheck-calculator.html\"><b>Paycheck Calculator<\/b><\/a><\/p>\n<div class=\"caution-box\">\n<p><span class=\"caution-label\">CAUTION<\/span> For an approximate match, these formulas use a very efficient search algorithm that assumes the lookup range is sorted in ascending order. If your data is not sorted, they don't return an error value, but the result may be unpredictable.<\/p>\n<p>If the lookup value is less than the first value in the lookup range, MATCH and VLOOKUP will return an error.<\/p>\n<\/div>\n<h2 id=\"2d-lookups\">4) 2D Lookups Using VLOOKUP-MATCH and INDEX-MATCH-MATCH<\/h2>\n<p>In this example, we'll do a mileage lookup between two cities. The formulas are basically the same as for a 1D lookup, except that we use a MATCH function to replace <i>col_index_num<\/i> in the VLOOKUP function and to replace <i>column_number<\/i> in the INDEX function.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/2d-lookup-using-vlookup-match.png\" alt=\"2D Lookup Using VLOOKUP-MATCH\" width=\"674\" height=\"209\" class=\"border imgshadow\"><\/p>\n<h3>Using VLOOKUP<\/h3>\n<pre class=\"XLformula light\">=<span class=\"function\">VLOOKUP<\/span>(<span class=\"arg\">row_lookup_value<\/span>,<span class=\"arg\">table_array<\/span>, <span class=\"function\">MATCH<\/span>(<span class=\"arg\">column_lookup_value<\/span>,<span class=\"arg\">column_label_range<\/span>,0), FALSE)\r\n<\/pre>\n<h3>Using INDEX-MATCH-MATCH<\/h3>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span><b>(<\/b> <span class=\"arg\">result_array<\/span>,\r\n<span class=\"function\">MATCH<\/span>(<span class=\"arg\">row_lookup_value<\/span>,<span class=\"arg\">row_label_range<\/span>,0),\r\n<span class=\"function\">MATCH<\/span>(<span class=\"arg\">column_lookup_value<\/span>,<span class=\"arg\">column_label_range<\/span>,0) <b>)<\/b>\r\n<\/pre>\n<h3>Using HLOOKUP<\/h3>\n<pre class=\"XLformula light\">=<span class=\"function\">HLOOKUP<\/span>(<span class=\"arg\">column_lookup_value<\/span>,<span class=\"arg\">table_array<\/span>, <span class=\"function\">MATCH<\/span>(<span class=\"arg\">row_lookup_value<\/span>,<span class=\"arg\">row_label_range<\/span>,0), FALSE)\r\n<\/pre>\n<h2 id=\"3d-lookups\">5) 3D Lookups Using INDEX-MATCH and VLOOKUP<\/h2>\n<p>To demonstrate a 3D lookup, we'll use mileage tables again, but this time we have a separate table for Road and Plane. The INDEX function allows you to return a value from a 3D array. You can replace the <i>row_number<\/i>, <i>column_number<\/i>, and <i>area_number<\/i> with 3 MATCH functions.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> The <i>reference<\/i> argument for the INDEX function should be multiple same-size ranges surrounded by parentheses like this: (A1:D10,E1:H10), or you can use a named range.<\/p>\n<\/div>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>( (<span class=\"arg\">array_range1<\/span>,<span class=\"arg\">array_range2<\/span>) ,\r\n<span class=\"function\">MATCH<\/span>(<span class=\"arg\">row_lookup_value<\/span>,<span class=\"arg\">row_label_range<\/span>,0),\r\n<span class=\"function\">MATCH<\/span>(<span class=\"arg\">column_lookup_value<\/span>,<span class=\"arg\">column_label_range<\/span>,0),\r\n<span class=\"function\">MATCH<\/span>(<span class=\"arg\">table_lookup_value<\/span>,{\"Road\",\"Plane\"},0) )\r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/3d-lookup-using-index-match.png\" alt=\"3D Lookup Using INDEX-MATCH\" width=\"674\" height=\"350\" class=\"border imgshadow\"><\/p>\n<h3>3D Lookup Using VLOOKUP<\/h3>\n<p>It is possible to do a 3D lookup using VLOOKUP. Starting with the 2D lookup formula, in place of <em>array_table<\/em>, you can use CHOOSE(<em>table_number<\/em>,<em>table_array_1<\/em>,<em>table_array_2<\/em>). You can use MATCH to find the value for  <i>table_number<\/i> as in the INDEX-MATCH example above. The resulting formula would look like this:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">VLOOKUP<\/span>(<span class=\"arg\">row_lookup_value<\/span>, <span class=\"function\">CHOOSE<\/span>( <span class=\"function\">MATCH<\/span>(<span class=\"arg\">table_name<\/span>,{\"Road\",\"Plane\"},0), <span class=\"arg\">road_table_array<\/span>, <span class=\"arg\">plane_table_array<\/span>), <span class=\"function\">MATCH<\/span>(<span class=\"arg\">column_lookup_value<\/span>,<span class=\"arg\">column_label_range<\/span>,0), FALSE)<\/pre>\n<h2 id=\"exact\">6) Case-Sensitive EXACT Lookup Using INDEX-MATCH<\/h2>\n<p>Most lookups and logical comparisons in Excel are NOT case-sensitive, meaning that both \"A\"=\"a\" and \"A\"=\"A\" would return TRUE.<\/p>\n<p>The <b>EXACT<\/b>(<i>value1<\/i>,<i>value2<\/i>) function allows you to make a comparison between <i>value1<\/i> and <i>value2<\/i> that IS case sensitive, so EXACT(\"A\",\"a\") returns FALSE and EXACT(\"B\",\"B\") returns TRUE.<\/p>\n<p>If you use EXACT to compare a value to a range like EXACT(\"B\",A1:A20), the function returns an <b>array<\/b> of TRUE and FALSE values. You can then use a MATCH function to look for the value TRUE within the range returned by EXACT(<i>lookup_value<\/i>,<i>lookup_range<\/i>). The final lookup formula is an <a href=\"\/blog\/excel-formulas\/array-formula-examples.html\">Excel Array Formula<\/a>, so you need to press <b>Ctrl+Shift+Enter<\/b> after entering the formula.<\/p>\n<pre class=\"XLformula light\">{Ctrl+Shift+Enter} =<span class=\"function\">INDEX<\/span>(<span class=\"arg\">result_range<\/span>, <span class=\"function\">MATCH<\/span>(TRUE,<span class=\"function\">EXACT<\/span>(<span class=\"arg\">lookup_value<\/span>,<span class=\"arg\">lookup_range<\/span>),0) )<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/case-sensitive-exact-lookup-using-index-match.png\" alt=\"Case-Sensitive EXACT Lookup Using INDEX-MATCH\" width=\"506\" height=\"189\" class=\"border imgshadow\"><\/p>\n<p>See the references at the end of this article if you are curious about how a case-sensitive lookup can be done with VLOOKUP.<\/p>\n<h2 id=\"multiple-criteria\">7) Multiple-Criteria Exact Lookups Using VLOOKUP and INDEX-MATCH<\/h2>\n<p>One way to do an exact-match using multiple criteria is to <b>concatenate<\/b> the lookup columns and do a lookup using the concatenated lookup values. VLOOKUP requires using a helper column containing the concatenated lookup columns. INDEX-MATCH does not need the helper column, but it becomes an array formula (Ctrl+Shift+Enter).<\/p>\n<h3>Multiple-criteria lookup using VLOOKUP and a helper column<\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/vlookup-with-multiple-criteria.png\" alt=\"VLOOKUP with Multiple Criteria\" width=\"663\" height=\"212\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">VLOOKUP<\/span>(<span class=\"arg\">value_1<\/span> &amp; <span class=\"arg\">value_2<\/span>,<span class=\"arg\">table_array<\/span>,<span class=\"arg\">col_index_num<\/span>,FALSE)<\/pre>\n<h3>Multiple-criteria lookup using INDEX-MATCH as an array formula<\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/index-match-with-multiple-criteria.png\" alt=\"INDEX-MATCH with Multiple Criteria\" width=\"663\" height=\"212\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula light\">{Ctrl+Shift+Enter}  =<span class=\"function\">INDEX<\/span>(<span class=\"arg\">result_range<\/span>,<span class=\"function\">MATCH<\/span>(<span class=\"arg\">value1<\/span> &amp; <span class=\"arg\">value2<\/span>, <span class=\"arg\">lookup_col1<\/span> &amp; <span class=\"arg\">lookup_col2<\/span>,0))<\/pre>\n<h2 id=\"non-exact\">8) Lookups with Multiple Non-Exact Criteria Using INDEX-MATCH<\/h2>\n<p>When you want to use logical conditions such as A &gt; B or A &lt; B in your lookup, a method I like is to use INDEX-MATCH and convert the <i>lookup_range<\/i> to a TRUE or FALSE expression like <i>lookup_range&lt;lookup_value<\/i>. Then search for the first occurrence of TRUE (using 1 as the first argument of the MATCH function). Using this method, you can have <b>any number of conditions<\/b> because multiplying true\/false expressions together acts like the logical AND operator. The formula must be entered as an array formula (Ctrl+Shift+Enter), but that's a small price to pay for relative simplicity.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/index-match-with-non-exact-criteria.png\" alt=\"INDEX-MATCH with Multiple Non-Exact Criteria\" width=\"484\" height=\"212\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula light\">{Ctrl+Shift+Enter}  =<span class=\"function\">INDEX<\/span>(<span class=\"arg\">result_range<\/span>,<span class=\"function\">MATCH<\/span>(1, (<span class=\"arg\">lookup_col1<\/span>&gt;<span class=\"arg\">lookup_value1<\/span>) * (<span class=\"arg\">lookup_col2<\/span>&lt;<span class=\"arg\">lookup_value2<\/span>) ,0) )<\/pre>\n<h2 id=\"last-numeric-value\">9) Return the Last Numeric Value in a Column<\/h2>\n<p>When using an approximate lookup for VLOOKUP and MATCH, you can return the last numeric value in a column if you use an extremely large number as the lookup value (to make sure it will be larger than any number in the lookup range). A common example would be to return the last value in a Balance column for a <a href=\"\/ExcelTemplates\/excel-checkbook.html\">checkbook register<\/a> as shown in the example below.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/return-last-numeric-value-in-column-vlookup.png\" alt=\"Return the Last Numeric Value in a Column - VLOOKUP\" width=\"451\" height=\"293\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">VLOOKUP<\/span>( 9E+100, <span class=\"arg\">lookup_column<\/span>, 1, TRUE)<\/pre>\n<p>Recall that the TRUE option (for an approximate match) is the default for VLOOKUP, so that is why the formula in the checkbook example image only shows 3 arguments.<\/p>\n<p>This can of course be done with INDEX-MATCH, but I prefer the VLOOKUP formula in this case because it requires only one reference to the lookup column.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>( <span class=\"arg\">lookup_column<\/span>, <span class=\"function\">MATCH<\/span>( 9E+99, <span class=\"arg\">lookup_column<\/span>, 1) )<\/pre>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTES<\/span> The <i>lookup_column<\/i> can contain text values and even errors (like #N\/A or #DIV\/0), and those values will be ignored. The formula returns only the last numeric value.<\/p>\n<\/div>\n<h2 id=\"last-text-value\">10) Return the Last Text Value in a Column<\/h2>\n<p>When searching for the last text value, instead of a large numeric value for the <i>lookup_value<\/i> as in the previous example, use a \"large\" text value. By that, I mean a text value that would show up last if you sorted a column in alphabetical order. If you are using the English alphabet without special characters, that could be \"zzzzzz.\" If you are using Greek characters or symbols in your list, you could try a <a href=\"\/blog\/help\/excel-help\/using-unicode-character-symbols-in-excel.html\">Unicode Character<\/a> such as \"&#128511;\" as the lookup value.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">VLOOKUP<\/span>( \"zzzzzzz\", <span class=\"arg\">lookup_column<\/span>, 1, TRUE)<\/pre>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>( <span class=\"arg\">lookup_column<\/span>, <span class=\"function\">MATCH<\/span>( \"&#128511;\", <span class=\"arg\">lookup_column<\/span>, 1) )<\/pre>\n<p>Rather than returning the last text value, I often use just the MATCH part of this formula to return the <b>row number<\/b> of the last value. This allows you to make a <a href=\"\/blog\/excel-formulas\/dynamic-named-ranges.html\">dynamic named range<\/a> that can be used as the source range for a <a href=\"\/blog\/help\/excel-help\/create-a-drop-down-list-in-excel.html\">drop-down list via data validation<\/a>.<\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"last-non-blank-value\">11) Return the Last Non-BLANK Value in a Column<\/h2>\n<p>If your column contains both text and numeric values, you may want a formula to return the last non-BLANK value. Using the INDEX-MATCH formula, we can search for the last numeric value and the last text value and return whichever comes last.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>( <span class=\"arg\">lookup_column<\/span>, <span class=\"function\">MAX<\/span>( <span class=\"function\">MATCH<\/span>( \"zzzzzz\", <span class=\"arg\">lookup_column<\/span>, 1), <span class=\"function\">MATCH<\/span>(9E+100, <span class=\"arg\">lookup_column<\/span>,1) ) )<\/pre>\n<p>A more concise formula uses the <b>LOOKUP<\/b> function. The LOOKUP function allows the <i>lookup_range<\/i> to be an expression rather than a direct reference (and it can be a row or column). We can use a logical comparison and search for the last TRUE value like this:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/last-non-blank-value-using-lookup.png\" alt=\"Return the Last Non-Blank Value Using LOOKUP\" width=\"393\" height=\"213\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>(42, 1\/<span class=\"function\">NOT<\/span>(<span class=\"function\">ISBLANK<\/span>(<span class=\"arg\">lookup_range<\/span>)), <span class=\"arg\">lookup_range<\/span>)<\/pre>\n<p>The trick here is that the expression 1\/NOT(ISBLANK(lookup_range)) returns an array of 1s for TRUE and #DIV\/0 errors for FALSE. LOOKUP ignores the error values so it will return the last value in <i>lookup_range<\/i> that is not blank. The number 42 is arbitrary - it just needs to be larger than 1 to return the last value.<\/p>\n<p>Using very similar formulas, you can use LOOKUP to return just the last numeric value or text value like this:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>( 42, 1\/<span class=\"function\">ISNUMBER<\/span>(<span class=\"arg\">lookup_range<\/span>), <span class=\"arg\">result_range<\/span>)<\/pre>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>( 42, 1\/<span class=\"function\">ISTEXT<\/span>(<span class=\"arg\">lookup_range<\/span>), <span class=\"arg\">result_range<\/span>)<\/pre>\n<h2 id=\"last-non-empty-value\">12) Return the Last Non-Empty Value Using LOOKUP<\/h2>\n<p>If you are using a formula to return an empty value \"\" and you want to ignore those cells when searching for the last value in the range, you can use the LOOKUP formula mentioned in the last example with the expression 1\/(lookup_range&lt;&gt;\"\").<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/return-the-last-non-empty-value-using-lookup.png\" alt=\"Return the Last Non-Empty Value\" width=\"393\" height=\"213\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>(42, 1\/(<span class=\"arg\">lookup_range<\/span>&lt;&gt;\"\"), <span class=\"arg\">lookup_range<\/span>)<\/pre>\n<p>If you want to return the relative row number instead of the actual value, you can use the following formula:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>(42, 1\/(<span class=\"arg\">lookup_range<\/span>&lt;&gt;\"\"), <span class=\"function\">ROW<\/span>(<span class=\"arg\">lookup_range<\/span>)-<span class=\"function\">ROW<\/span>(<span class=\"arg\">first_cell_in_lookup_range<\/span>)+1 )<\/pre>\n<h2 id=\"lookup-nth-match\">13) Lookup based on the Nth Match<\/h2>\n<p>You can use the SMALL function to return the Nth smallest value from an array, and we can use that along with an array formula and the INDEX function to do a lookup based on the Nth match. See my <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/array-formula-examples.html\">Array Formula Examples<\/a> article to learn more about Array Formulas and specifically the SMALL-IF formula.<\/p>\n<p>In this example, we want to return the <b>2nd<\/b> Event that matches the date 3\/7\/2018. Note: The curly { } brackets in the formula below are a reminder to press Ctrl+Shift+Enter to enter the formula as an Array Formula. You don't actually type the brackets into the formula.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/lookup-nth-match-example.png\" alt=\"Lookup Based on the Nth Match\" width=\"669\" height=\"233\" class=\"border imgshadow\"><\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span>  =<span class=\"function\">INDEX<\/span>(<span class=\"arg\">result_range<\/span>,<span class=\"function\">SMALL<\/span>( <span class=\"function\">IF<\/span>(<span class=\"arg\">lookup_range<\/span>=<span class=\"arg\">lookup_value<\/span>, <span class=\"function\">ROW<\/span>(<span class=\"arg\">lookup_range<\/span>)-<span class=\"function\">ROW<\/span>(<span class=\"arg\">first_cell_in_lookup_range<\/span>)+1),<span class=\"arg\">occurrence<\/span>))  <span class=\"green\">}<\/span><\/pre>\n<p>How does this work? The SMALL-IF part of the formula is acting kind of like the MATCH function except that it is returning the index number for the 2nd occurrence of the match. We can use SMALL in this example because Excel stores dates as numbers.<\/p>\n<p>This formula is used within the <a href=\"https:\/\/www.vertex42.com\/calendars\/daily-planner.html\">Daily Planner Template<\/a> to list events and holidays occurring on a particular date.<\/p>\n<h2>14) VLOOKUP vs. INDEX-MATCH<\/h2>\n<p>Excel people like to debate about whether VLOOKUP is better than INDEX-MATCH for lookup formulas. The most common argument is that VLOOKUP is simpler and INDEX-MATCH is more powerful. Even though I usually prefer INDEX-MATCH, I think both formulas are pretty simple, and one isn't necessarily always more powerful than the other.<\/p>\n<p>If you want to do more advanced lookups with VLOOKUP, then you'll probably need to learn how to use MATCH and CHOOSE. If you want to become a power Excel user, then you'll also want to learn the INDEX function. So, my opinion on the VLOOKUP vs. INDEX-MATCH debate is to learn how to use them all.<\/p>\n<p>In conclusion, here are some reminders applicable to both VLOOKUP and INDEX-MATCH:<\/p>\n<ul class=\"bulletlist\">\n<li>Don't forget the FALSE or 0 option if you are wanting an exact match, because the default parameter is to use an approximate match.<\/li>\n<li>As a general rule, use absolute ($A$1) cell references to refer to the lookup ranges and table arrays because when you copy the lookup formula you will usually want those ranges to remain the same.<\/li>\n<li>Check for extra blank spaces if you think a lookup formula should be finding a match, but it is not.<\/li>\n<li>Use IFERROR to handle the error returned when an exact match is not found.<\/li>\n<\/ul>\n<h2>References<\/h2>\n<div class=\"refblock\">\n<ul>\n<li><a href=\"\/ExcelTips\/workbook.html\">Spreadsheet Tips Workbook<\/a> - <i>vertex42.com<\/i> - by Jon Wittwer and Brent Weight<\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1\" target=\"_blank\" rel=\"noopener noreferrer\">VLOOKUP Function<\/a> - <i>support.office.com<\/i> - The official documentation of the VLOOKUP function.<\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/MATCH-function-e8dffd45-c762-47d6-bf89-533f4a37673a\" target=\"_blank\" rel=\"noopener noreferrer\">MATCH Function<\/a> - <i>support.office.com<\/i> - The official documentation of the MATCH function.<\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/INDEX-function-a5dcf0dd-996d-40a4-a822-b56b061328bd\" target=\"_blank\" rel=\"noopener noreferrer\">INDEX Function<\/a> - <i>support.office.com<\/i> - The official documentation of the INDEX function.<\/li>\n<li><a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2014\/08\/19\/vlookup-case-sensitive-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">Case-Sensitive Match Using VLOOKUP<\/a> - at <i>ablebits.com<\/i> - This shows that it IS possible, but the solution is quite complex.<\/li>\n<li><a href=\"https:\/\/exceljet.net\/formula\/get-value-of-last-non-empty-cell\" target=\"_blank\" rel=\"noopener noreferrer\">Get Value of Last Non-Empty Cell<\/a> - at <i>exceljet.net<\/i> - I think this article explains the LOOKUP function better than I did.<\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to use both VLOOKUP and INDEX-MATCH and other powerful formulas to do lookups in Excel, from simple to advanced formulas, including multiple criteria and case-sensitive lookups and to get the last value in a range.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79],"tags":[],"class_list":{"0":"post-3407","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3407","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3407"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3407\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3407"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3407"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3407"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}