{"id":28589,"date":"2023-04-12T09:15:36","date_gmt":"2023-04-12T07:15:36","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=28589"},"modified":"2024-02-06T16:21:15","modified_gmt":"2024-02-06T15:21:15","slug":"index-and-match-made-simple","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/index-and-match-made-simple\/","title":{"rendered":"INDEX and MATCH Made Simple"},"content":{"rendered":"<p><em><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/301F3724-DA31-462A-ABE6-D625B4CDD75D.png\" target=\"_blank\"><br \/>\n<img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter wp-image-28590 size-large\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/301F3724-DA31-462A-ABE6-D625B4CDD75D-1024x576.png\" alt=\"INDEX and MATCH Made Simple\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/301F3724-DA31-462A-ABE6-D625B4CDD75D-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/301F3724-DA31-462A-ABE6-D625B4CDD75D-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/301F3724-DA31-462A-ABE6-D625B4CDD75D-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/301F3724-DA31-462A-ABE6-D625B4CDD75D.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/em><\/p>\n<p>INDEX and MATCH are the go-to Excel functions for carrying out sophisticated lookups, owing to their high degree of flexibility. With these functions, you can execute both vertical and horizontal lookups, 2-way lookups, left lookups, case-sensitive lookups, and even perform lookups based on multiple criteria. To enhance your Excel abilities, it is recommended that you add INDEX and MATCH to your skill set. Below are numerous examples to help you get started.<\/p>\n<p>This article offers a straightforward guide on utilizing INDEX and MATCH together to execute lookups. It adopts a methodical approach by first outlining the individual functions of INDEX and MATCH, and then demonstrating how to merge both functions to achieve a versatile two-way lookup. You will also find more advanced examples later on in the article.<\/p>\n<p>Here are some of the techniques we will explore:<\/p>\n<ul>\n<li><a href=\"#1\"><strong>The INDEX Function<\/strong><\/a><\/li>\n<li><a href=\"#2\"><strong>The MATCH Function<\/strong><\/a><\/li>\n<li><a href=\"#3\"><strong>INDEX and MATCH together<\/strong><\/a><\/li>\n<li><a href=\"#4\"><strong>Two-way lookup with INDEX and MATCH<\/strong><\/a><\/li>\n<li><a href=\"#5\"><strong>Left Lookup<\/strong><\/a><\/li>\n<li><a href=\"#6\"><strong>Case-sensitive Lookup<\/strong><\/a><\/li>\n<\/ul>\n<p>Let&#8217;s look at each of these ways thoroughly!<\/p>\n<p><strong><span id=\"1\" style=\"color: #ff0000!important;\">The INDEX Function<\/span><\/strong><\/p>\n<p>Excel&#8217;s INDEX function is a remarkably flexible and potent tool that is featured in many advanced <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formulas\" data-wpil-keyword-link=\"linked\">formulas<\/a>. So, what does INDEX do? Simply put, it retrieves the value at a specific position in a range.<\/p>\n<p>To illustrate, suppose you have a list of beverages in your menu, and you wish to obtain the name of the beverage, which is Lemonade, using a <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a>. You can utilize INDEX in the following way<\/p>\n<p>Suppose you want to retrieve the price of the Lemonade using the INDEX function. In this instance, you can specify both a row number and a column number while providing a larger range. The following INDEX formula utilizes the complete data range of A2:C10, with a row number of 4 and a column number of 2:<\/p>\n<p><span class=\"Apple-converted-space\">\u00a0<\/span><strong>=INDEX(A2:C10,4,2)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/06DE4EED-D1B8-431E-9D34-9C7A9C441CBA.jpeg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-full wp-image-28596\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/06DE4EED-D1B8-431E-9D34-9C7A9C441CBA.jpeg\" alt=\"INDEX and MATCH Made Simple\" width=\"940\" height=\"478\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/06DE4EED-D1B8-431E-9D34-9C7A9C441CBA.jpeg 940w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/06DE4EED-D1B8-431E-9D34-9C7A9C441CBA-300x153.jpeg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/06DE4EED-D1B8-431E-9D34-9C7A9C441CBA-768x391.jpeg 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><\/a><\/p>\n<p><span class=\"Apple-converted-space\">\u00a0<\/span>Suppose you want to retrieve the price of the Lemonade using the INDEX function. In this instance, you can specify both a row number and a column number while providing a larger range. The following INDEX formula utilizes the complete data range of A2:C10, with a row number of 4 and a column number of 2, which corresponds to the price:<\/p>\n<p><strong>=INDEX(A2:A10,4)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/B238C2F8-CBD5-4A72-B821-4A38C0C2E863.jpeg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-full wp-image-28597\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/B238C2F8-CBD5-4A72-B821-4A38C0C2E863.jpeg\" alt=\"INDEX and MATCH Made Simple\" width=\"940\" height=\"378\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/B238C2F8-CBD5-4A72-B821-4A38C0C2E863.jpeg 940w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/B238C2F8-CBD5-4A72-B821-4A38C0C2E863-300x121.jpeg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/B238C2F8-CBD5-4A72-B821-4A38C0C2E863-768x309.jpeg 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><\/a><\/p>\n<p>In summary, the INDEX function retrieves a value at a specific location in a range of cells based on a numerical position. If the range is one-dimensional, a row number is adequate. If the range is two-dimensional, both the row and column numbers are required.<\/p>\n<p>However, it is uncommon to know the position of data in a <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-excel-templates-and-spreadsheets\/\" target=\"_blank\" rel=\"noopener\" title=\"spreadsheet\" data-wpil-keyword-link=\"linked\">spreadsheet<\/a>.<span class=\"Apple-converted-space\">\u00a0<\/span><\/p>\n<p>Thus, we require a mechanism to locate the positions of data we are seeking. This is where the MATCH function comes in handy.<\/p>\n<p><strong><span id=\"2\" style=\"color: #ff0000!important;\">The MATCH Function<\/span><\/strong><\/p>\n<p>The MATCH function has a specific function: to determine the position of an item in a range of cells. For instance, you can use MATCH to locate the position of the word &#8220;barley&#8221; in a list of grains, like this:<\/p>\n<p><strong>=MATCH(\u201cbarley\u201d,A2:A8,0)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/4CD967D0-2C50-4794-8151-1335B343D4D3.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-full wp-image-28598\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/4CD967D0-2C50-4794-8151-1335B343D4D3.png\" alt=\"INDEX and MATCH Made Simple\" width=\"881\" height=\"438\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/4CD967D0-2C50-4794-8151-1335B343D4D3.png 881w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/4CD967D0-2C50-4794-8151-1335B343D4D3-300x149.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/4CD967D0-2C50-4794-8151-1335B343D4D3-768x382.png 768w\" sizes=\"(max-width: 881px) 100vw, 881px\" \/><\/a><\/p>\n<p>As you can see, it does not matter if the range is vertical or horizontal:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/118B601A-46E0-488F-A7BD-944ACF1EBE9C.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-large wp-image-28600\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/118B601A-46E0-488F-A7BD-944ACF1EBE9C-1024x271.png\" alt=\"INDEX and MATCH Made Simple\" width=\"1024\" height=\"271\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/118B601A-46E0-488F-A7BD-944ACF1EBE9C-1024x271.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/118B601A-46E0-488F-A7BD-944ACF1EBE9C-300x79.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/118B601A-46E0-488F-A7BD-944ACF1EBE9C-768x203.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/118B601A-46E0-488F-A7BD-944ACF1EBE9C.png 1306w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>It is crucial to note that the last argument in the MATCH function is match_type. This argument determines whether the matching should be exact or approximate. In most cases, you will want to use the value 0 to enforce exact match behavior. The match_type parameter defaults to 1, indicating approximate match, so it is important to specify a value to avoid any confusion.<\/p>\n<p><strong><span id=\"3\" style=\"color: #ff0000!important;\">INDEX and MATCH together<\/span><\/strong><\/p>\n<p>To combine the INDEX and MATCH functions in a single formula, you first need to understand that INDEX returns a value from a range based on a row and column number. Therefore, you can use MATCH to find the row or column number that you need to retrieve from the range.<\/p>\n<p>For example, consider the data below, which represents a table of produce and daily sales numbers for three days: Monday, Tuesday, and Wednesday.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/700BB3E4-01FE-48FD-99D0-88329C0A2A34.jpeg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-full wp-image-28603\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/700BB3E4-01FE-48FD-99D0-88329C0A2A34.jpeg\" alt=\"INDEX and MATCH Made Simple\" width=\"884\" height=\"546\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/700BB3E4-01FE-48FD-99D0-88329C0A2A34.jpeg 884w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/700BB3E4-01FE-48FD-99D0-88329C0A2A34-300x185.jpeg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/700BB3E4-01FE-48FD-99D0-88329C0A2A34-768x474.jpeg 768w\" sizes=\"(max-width: 884px) 100vw, 884px\" \/><\/a><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/2400BF60-9D4A-4AFF-84D5-C98ED3702BA5.png\" target=\"_blank\"><br \/>\n<\/a><\/p>\n<p>Suppose we aim to create a formula that provides the sales number for Tuesday of a specific item. Based on our previous discussion, we are aware that we can provide INDEX with a row and column number to obtain a value. To illustrate, to obtain the Tuesday sales number for Pineapple, we need to specify the range C3:E11 with a row of 5 and a column of 2.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/6D40E597-00DE-40A0-986A-8EB190A59B01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-full wp-image-28604\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/6D40E597-00DE-40A0-986A-8EB190A59B01.png\" alt=\"INDEX and MATCH Made Simple\" width=\"953\" height=\"443\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/6D40E597-00DE-40A0-986A-8EB190A59B01.png 953w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/6D40E597-00DE-40A0-986A-8EB190A59B01-300x139.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/6D40E597-00DE-40A0-986A-8EB190A59B01-768x357.png 768w\" sizes=\"(max-width: 953px) 100vw, 953px\" \/><\/a><\/p>\n<p>However, you don\u2019t want to hardcode numbers. The dynamic lookup is the difference-maker.<span class=\"Apple-converted-space\">\u00a0<\/span><\/p>\n<p>How can we achieve that? We can utilize the MATCH function, which is ideal for locating the positions we require. To begin, we can hardcode the column as 2 and make the row number adaptable by using MATCH. Here&#8217;s the updated formula, where the MATCH function is inserted inside INDEX in place of 5:<\/p>\n<p><strong>=INDEX(C3:E11,MATCH(\u201cPineapple\u201d,B3:B11,0),2)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/90EF74BE-C0C7-466A-885E-19873CC653B2.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter wp-image-28607 size-large\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/90EF74BE-C0C7-466A-885E-19873CC653B2-1024x449.png\" alt=\"INDEX and MATCH Made Simple\" width=\"1024\" height=\"449\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/90EF74BE-C0C7-466A-885E-19873CC653B2-1024x449.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/90EF74BE-C0C7-466A-885E-19873CC653B2-300x131.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/90EF74BE-C0C7-466A-885E-19873CC653B2-768x337.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/90EF74BE-C0C7-466A-885E-19873CC653B2.png 1159w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>Taking things one step further, we&#8217;ll use the value from H2 in\u00a0MATCH:<\/p>\n<p><strong>=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/9CA3E626-2B1A-4A4E-97A8-D746EEAE1F17.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-large wp-image-28606\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/9CA3E626-2B1A-4A4E-97A8-D746EEAE1F17-1024x394.png\" alt=\"INDEX and MATCH Made Simple\" width=\"1024\" height=\"394\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/9CA3E626-2B1A-4A4E-97A8-D746EEAE1F17-1024x394.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/9CA3E626-2B1A-4A4E-97A8-D746EEAE1F17-300x116.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/9CA3E626-2B1A-4A4E-97A8-D746EEAE1F17-768x296.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/9CA3E626-2B1A-4A4E-97A8-D746EEAE1F17-1536x592.png 1536w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/9CA3E626-2B1A-4A4E-97A8-D746EEAE1F17.png 1672w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>In summary:<\/p>\n<ul>\n<li>INDEX needs numeric positions.<\/li>\n<li>MATCH locates those positions.<\/li>\n<li>MATCH is enclosed within INDEX.<\/li>\n<\/ul>\n<p><strong><span id=\"4\" style=\"color: #ff0000!important;\">Two-way lookup with INDEX and MATCH<\/span><\/strong><\/p>\n<p>In the previous example, we utilized the MATCH function to dynamically determine the row number, but the column number was hardcoded. Is there a way to create a completely flexible formula that can return data for any product in any day? To achieve this, we need to use MATCH twice: one to determine the row position and the other to determine the column position.<\/p>\n<p>Based on the examples provided, it is evident that MATCH works effectively with arrays in both horizontal and vertical orientations. This implies that locating the position of a specified month using MATCH is a simple task. To illustrate, the following formula identifies the position of Wednesday, which is 3:<\/p>\n<p><strong>=MATCH(\u201cWednesday\u201d, C2:E2,0) \/\/ returns 3<\/strong><\/p>\n<p>Naturally, it is preferable not to hardcode values. Therefore, let us modify the worksheet to enable the input of a day\u2019s name and utilize MATCH to locate the relevant column number:<\/p>\n<p><strong>=INDEX(C3:E11,MATCH(H2,B3:B11,0),MATCH(H3,C2:E2,0))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/5A797741-E7E5-40F7-BD97-18FA73CB0606.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-large wp-image-28608\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/5A797741-E7E5-40F7-BD97-18FA73CB0606-1024x433.png\" alt=\"INDEX and MATCH Made Simple\" width=\"1024\" height=\"433\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/5A797741-E7E5-40F7-BD97-18FA73CB0606-1024x433.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/5A797741-E7E5-40F7-BD97-18FA73CB0606-300x127.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/5A797741-E7E5-40F7-BD97-18FA73CB0606-768x324.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/5A797741-E7E5-40F7-BD97-18FA73CB0606.png 1501w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>After the initial MATCH formula returns 5 to INDEX as the row number, and the second MATCH formula returns 3 to INDEX as the column number, the formula is simplified to:<\/p>\n<p><strong>=INDEX(C3:E11,5,3)<\/strong><\/p>\n<p>and INDEX\u00a0correctly returns 11, the quantity of Pineapples sold on Wednesday.<\/p>\n<p><strong><span id=\"5\" style=\"color: #ff0000!important;\">Left Lookup<\/span><\/strong><\/p>\n<p>INDEX and MATCH offer a significant advantage over the VLOOKUP function, which is the capability to perform a &#8220;left lookup.&#8221; Essentially, this refers to a search in which the identifier column is located to the right of the values that require retrieval:<\/p>\n<p><strong>=INDEX(B3:B11,MATCH(14,F3:F11,0))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/E34C774E-CB66-4CBE-A370-47F40BE855CB.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-large wp-image-28609\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/E34C774E-CB66-4CBE-A370-47F40BE855CB-1024x572.png\" alt=\"INDEX and MATCH Made Simple\" width=\"1024\" height=\"572\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/E34C774E-CB66-4CBE-A370-47F40BE855CB-1024x572.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/E34C774E-CB66-4CBE-A370-47F40BE855CB-300x168.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/E34C774E-CB66-4CBE-A370-47F40BE855CB-768x429.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/E34C774E-CB66-4CBE-A370-47F40BE855CB.png 1343w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>We can easily find out the onion\u2019s total sales by using the VLOOKUP function. The left LOOKUP is the opposite of that.<span class=\"Apple-converted-space\">\u00a0 <\/span>If we wanted to find out which product got the lowest amount of sales (14), it would lead to the onion.<span class=\"Apple-converted-space\">\u00a0<\/span><\/p>\n<p><strong><span id=\"6\" style=\"color: #ff0000!important;\">Case-sensitive Lookup<\/span><\/strong><\/p>\n<p>Although the MATCH function does not consider the case sensitivity, you can incorporate the EXACT function along with INDEX and MATCH to carry out a lookup that takes into account the upper and lower case, as demonstrated below:<\/p>\n<p><strong>=INDEX(C3:C11,MATCH(TRUE,EXACT(G4,B3:B11),0))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/34E657F0-C8B5-4DAF-ABBB-3EE30265B179.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX and MATCH Made Simple\"  class=\"aligncenter size-large wp-image-28610\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/34E657F0-C8B5-4DAF-ABBB-3EE30265B179-1024x431.png\" alt=\"INDEX and MATCH Made Simple\" width=\"1024\" height=\"431\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/34E657F0-C8B5-4DAF-ABBB-3EE30265B179-1024x431.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/34E657F0-C8B5-4DAF-ABBB-3EE30265B179-300x126.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/34E657F0-C8B5-4DAF-ABBB-3EE30265B179-768x324.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/34E657F0-C8B5-4DAF-ABBB-3EE30265B179.png 1500w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>There you have it! In this article, we have covered how to use the INDEX and MATCH functions in <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\" title=\"Microsoft Excel\" data-wpil-keyword-link=\"linked\">Microsoft Excel<\/a>, as well as a few others. As you can see, these lookup tools can be quite powerful in the right setting!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>INDEX and MATCH are the go-to Excel functions for carrying out sophisticated lookups, owing to their high degree of flexibility. With these functions, you can execute both vertical and horizontal lookups, 2-way lookups, left lookups, case-sensitive lookups, and even perform lookups based on multiple criteria. To enhance your Excel abilities, it is recommended that you [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":28591,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,277,497,11,280,927,276],"tags":[1187,178,1186,78,82,1135,62],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/28589"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=28589"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/28589\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/28591"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=28589"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=28589"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=28589"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}