Now, instead of pointing to Row Number, type =Match – As we are going to use the Match Function to point to the location of Item Name (Chromebook) in our Data. In this ArticleINDEX & MATCH, The Perfect PairLookup to the LeftTwo-dimension lookupMultiple sectionsGoogle Sheets INDEX & MATCH This tutorial will teach you how to use the INDEX & MATCH combination to perform lookups in Excel and Google Sheets. Select E1:E10 as the INDEX Array – This is where the Price of items is located.Ĥ. INDEX() returns the value of a cell in a table based on the column and row number.
Next, place the curser in Cell B13 and start typing =INDEX – This will bring up the Syntax of Index Function.ģ. What is INDEX MATCH in Excel The INDEX MATCH formula is the combination of two functions in Excel Excel Resources Learn Excel online with 100s of free Excel tutorials, resources, guides & cheat sheets CFIs resources are the best way to learn Excel on your own terms.: INDEX and MATCH. Type the Name of the item (MacBook) for which price is required in Cell A13.Ģ. To illustrate the use of INDEX and MATCH Functions in combination, let us try to find the Price of MacBook from a Sales Data listing different types of computers sold along with their selling prices.ġ. So, let us go ahead and take a look at the steps to use INDEX and MATCH Functions in combination to perform a faster lookup. Now, changing the article number, we will see who was bought it, amount and how much is.This GPS like accuracy is achieved by using INDEX function to identify ‘Data Array’ (where info to Lookup is located) and MATCH Function to pinpoint the location of Item (for which info is required).Īs you can see in above image, the INDEX Function identifies the location of Price Column and the Match Function pinpoints the Location of Item (Chromebook). Let`s extend the formula to the remaining rows and check up. We see, that the article 3516 is really in peanuts. As the result looks like the formula, you can see in the screenshot above.
The syntax of the INDEX function is completed. The viewed array: the header with names, because the system will search for the word PRODUCT. The desired value will be the cell E14, where the name of the parameter we are looking for (PRODUCT) is indicated. On this, the arguments for MATCH have ended.
In the program, it is listed as 0 (zero). We have the specific article, so we choose to the exact match. Excel offers three types of matching: larger, smaller, and exact match. Because we search for the article, so we highlight to the column of articles together with the cap and to fix the F4. In our case, this is the cell in which the article is indicated the F13. We write down the MATCH command and put its arguments. It will look for the necessary position every time we change the article. But since we need, so that the result to change, we use the MATCH function.
If we were to withdraw one value, we would write a specific figure. You need to highlight it together with the cap and fixwith the F4 key. In this case, this is the entire table of orders. We write it down and study the syntax in parallel. Now you need to do so, that when you select the article, the values in the remaining four lines are automatically displayed. So we got the drop-down list of articles, that we can choose. And as the source, we highlight the column with the articles, including the cap. In the window that opens, in the «Allow:» section, select the «List». To do this, you need to click on the corresponding cell (we have the F13), then to select the DATA - Data Tools - Consolidate. To do this, the INDEX function together with MATCH will help.įor a start, let`s create the drop-down list for the ARTICLE OF PRODUCT field, so that you do not need to enter numbers from the keyboard, but select ones. Our goal: to create an order card, where by the article number you can see what kind of product it is, which customer it purchased, how much products were bought and at what total cost. We have the summary table, in which the purchased products are recorded. Let`s consider the interesting example that will allow us to understand the fascination of the INDEX function and the invaluable help of MATCH function. The example of using the INDEX and MATCH functions