MATCH, INDEX, and OFFSET<\/strong>.<\/p>\n\n\n\nCombining MATCH, INDEX, and OFFSET<\/h2>\n\n\n\n For our example, we will use the list of different products that were sold in different periods (months)<\/strong>. This will what our table will look like:<\/p>\n\n\n\n <\/figure>\n\n\n\nWe will first show the MATCH formula<\/strong> and then go from that. We will write our formula in column G, cells G2 and G3<\/strong>. Our formulas will be:<\/p>\n\n\n\n=MATCH(\"Product B\",B1:D1,0)\n=MATCH(\"Mar\",A2:A6,0)<\/code><\/pre>\n\n\n\nAnd the results for these formulas will be 2<\/strong> (position of Product B in the list of products), and 3 (position of the month of March in the list of months)<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nNow, the MATCH formula<\/strong> is pretty obvious. It finds us the relative position of the lookup value in our array.<\/p>\n\n\n\nFor the next thing, we will insert the INDEX and MATCH<\/strong> formulas together:<\/p>\n\n\n\n=INDEX(B2:D6,G3,G2)<\/code><\/pre>\n\n\n\nIn the table, it looks like this:<\/p>\n\n\n\n <\/figure>\n\n\n\nAs seen in the formula, our array is B2:D6<\/strong>, our row number will be the number 3<\/strong> (the row that corresponds to the month of March), and the column number will be number 2<\/strong> (the column of Product B).<\/p>\n\n\n\nWe use INDEX to get the number $117,680<\/strong>.<\/p>\n\n\n\nTo use OFFSET <\/strong>in combination with these two formulas, we will write down the following formula:<\/p>\n\n\n\n=INDEX(OFFSET(A1,MATCH(\"Product B\",B1:D1,0),MATCH(\"Mar\",A2:A6,0)),1,1)<\/code><\/pre>\n\n\n\nThis formula will return number<\/strong> $131,965<\/strong>, located in cell D3<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nBut how did we get here? First, we have to look at MATCH formulas<\/strong>. First one: MATCH(\u201cProduct B\u201d, B1:D1,0) <\/strong>searches for Product B in the first row<\/strong>, and it will find it in cell C1<\/strong>, or position number 2<\/strong>. Second MATCH function: MATCH(“Mar”, A2:A6,0)<\/strong> searches for the month of March<\/strong> in the list of our months, and it is located in position number 3<\/strong>.<\/p>\n\n\n\nOFFSET function<\/strong> has three mandatory parameters:<\/strong> reference, rows, and columns<\/strong>, and two optional (height and weight)<\/strong>. We first declare our reference cell and then define the number of rows and columns that we will offset from this original cell.<\/p>\n\n\n\nOFFSET function<\/strong> will return a range that starts from cell A1 (OFFSET(A1, part of the formula)<\/strong> and shifts from this cell by the number of rows and columns that we defined in our MATCH functions<\/strong>. First MATCH is the offset for rows from our defined cell, so we will move for 2 rows<\/strong> from our number. The second MATCH<\/strong> is the offset for columns, and we will move for 3 columns<\/strong> from column A<\/strong>.<\/p>\n\n\n\nIn the end, INDEX(range,1,1)<\/strong> retrieves the value at the intersection of the range obtained from OFFSET<\/strong>. The 1s as arguments<\/strong> refer to the first row and first column of the range.<\/p>\n","protected":false},"excerpt":{"rendered":"All of the formulas in Excel are great on their own. Better yet, these formulas are even better when combined. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170],"yoast_head":"\n
Combining MATCH, INDEX, and OFFSET in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n