This performs a search for the value in cell G2 within the A2 to A9 range, and requires an exact match. Shown separately, each function would read as follows: =MATCH(G2, A2:A9,0) We will use MATCH to determine where in the list the product number falls (position number), and we will use INDEX to return the values from the corresponding columns (Paper Type, Price, or In Stock,) by their column numbers. We can apply that to our simple query which extracts values from a dataset using the Product Number as an input, or lookup value. Nesting a formula means using one entire formula as an argument of another function. Combining the INDEX and MATCH functionsĪs mentioned before, the INDEX and MATCH formulas, when nested, can perform a lookup which accomplishes what the VLOOKUP does and more. The third item in column 2 is identified as the value “Matte” and returned as the output in cell F2. In the spreadsheet below, we want to display the value which is in the third row, second column of the array by using the INDEX function. Column_num is optional, but is required if row_num is omitted. Column_num is the column number where the cell containing the return value is to be found.If row_num is omitted, column_num is required. Row_num can be omitted if the array consists of only one row. Row_num is the row number where the cell containing the return value is to be found.Array refers to the range or array containing the data to be indexed.The syntax of the INDEX array function is =INDEX(array, row_num, ) We’ll focus on the array format, since this is the format you’ll need to learn for the INDEX/MATCH combo. The reference format is used when we want Excel to return the cell reference of the result cell (e.g. The array format is used when we want to return the value that is found in the result cell. The INDEX function has two formats - the array format and the reference format. The INDEX function returns a value or cell reference from within a table or range. Note also that the MATCH function is not case sensitive. If the lookup value is a cell reference, no double quotes are used. Note that in the example above, the value “matte” was typed directly into the formula between double quotation marks. If omitted, a match type of 1 is assumed.įor instance, if we wanted to know the position number of the word “matte” within the range B2 to B9 below. Match type -1 accepts a near-match rounded up to the next available value.Match type 1 accepts a near-match, rounded down to the next available value.Match_type is a setting which tells Excel whether you will accept a near-match if the lookup_value is not found in the lookup array.Lookup_array is the range of cells where the lookup value will be found.Lookup_value is the known value which you will be using to conduct a lookup, or search.The MATCH function has three possible arguments, with the following syntax: =MATCH(lookup_value, lookup_array, ) The MATCH function is used to determine the position number of a known value within a range of cells. Let’s break down both functions individually.
#INDEX MATCH EXCEL HOW TO#
Learning how to use INDEX MATCH is a great workaround. If you use VLOOKUP regularly, you would have encountered these problems and may have just decided to live with them. Again, you may not have this much control over the data in your lookup array. Lists sorted in descending order will return incorrect results. When approximate matches are accepted, (last argument omitted or TRUE,) the lookup values in the source data must be sorted in ascending order.For example, if the query above was designed for the user to enter the Paper Type, VLOOKUP would be unable to return the Product Number with the current layout of the dataset. It sounds simple, but it’s true, and it becomes a really big problem with data that we cannot change or reorganize. VLOOKUP cannot return a value that is to the left of the lookup value in the source array. With VLOOKUP, the lookup value must always be in the first column of the lookup array.This is great, but there are certain restrictions within VLOOKUP. VLOOKUP uses the Product Number which is manually entered in G2 to return each respective value, and the last argument (FALSE) in the VLOOKUP syntax ensures that Excel looks for an exact match between the product number and the first column in the source dataset. Cells F2 to G5 act like a simple query tool to extract information from the dataset in cells A2 to D9.