The tutorial showcases a few different formulas to perform two dimensional lookup in Excel. Just look through the alternatives and choose your favorite :)
When searching for something in your Excel spreadsheets, most of the time you'd look up vertically in columns or horizontally in rows. But sometimes you need to look across both rows and columns. In other words, you aim to find a value at the intersection of a certain row and column. This is called matrix lookup (aka 2-dimensional or 2-way lookup), and this tutorial shows how to do it in 4 different ways.
Excel INDEX MATCH MATCH formula
The most popular way to do a two-way lookup in Excel is by using INDEX MATCH MATCH. This is a variation of the classic INDEX MATCH formula to which you add one more MATCH function in order to get both the row and column numbers:
INDEX (data_array, MATCH (vlookup_value, lookup_column_range, 0), MATCH (hlookup value, lookup_row_range, 0))
As an example, let's make a formula to pull a population of a certain animal in a given year from the table below. For starters, we define all the arguments:
- Data_array - B2:E4 (data cells, not including row and column headers)
- Vlookup_value - H1 (target animal)
- Lookup_column_range - A2:A4 (row headers: animal names) - A3:A4
- Hlookup_value - H2 (target year)
- Lookup_row_range - B1:E1 (column headers: years)
Put all the arguments together and you will get this formula for two-way lookup:
=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))
If you need to do a two-way lookup with more than two criteria, take a look at this article: INDEX MATCH with multiple criteria in rows and columns.
How this formula works
While it may look a bit complex at first glance, the formula's logic is really straightforward and easy to understand. The INDEX function retrieves a value from the data array based on the row and column numbers, and two MATCH functions supply those numbers:
INDEX(B2:E4, row_num, column_num)
Here, we leverage the ability of MATCH(lookup_value, lookup_array, [match_type]) to return a relative position of lookup_value in lookup_array.
So, to get the row number, we search for the animal of interest (H1) across the row headers (A2:A4):
MATCH(H1, A2:A4, 0)
To get the column number, we search for the target year (H2) across the column headers (B1:E1):
MATCH(H2, B1:E1, 0)
In both cases, we look for exact match by setting the 3rd argument to 0.
In this example, the first MATCH returns 2 because our vlookup value (Polar bear) is found in A3, which is the 2nd cell in A2:A4. The second MATCH returns 3 because the hlookup value (2000) is found in D1, which is the 3rd cell in B1:E1.
Given the above, the formula reduces to:
INDEX(B2:E4, 2, 3)
And return a value at the intersection of the 2nd row and 3rd column in the data array B2:E4, which is a value in the cell D3.
VLOOKUP and MATCH formula for 2-way lookup
Another way to do a two-dimensional lookup in Excel is by using a combination of VLOOKUP and MATCH functions:
VLOOKUP(vlookup_value, table_array, MATCH(hlookup_value, lookup_row_range, 0), FALSE)
For our sample table, the formula takes the following shape:
=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)
Where:
- Table_array - A2:E4 (data cells including row headers)
- Vlookup_value - H1 (target animal)
- Hlookup_value - H2 (target year)
- Lookup_row_range - A1:E1 (column headers: years)
How this formula works
The core of the formula is the VLOOKUP function configured for exact match (the last argument set to FALSE), which searches for the lookup value (H1) in the first column of the table array (A2:E4) and returns a value from another column in the same row. To determine which column to return a value from, you use the MATCH function that is also configured for exact match (the last argument set to 0):
MATCH(H2, A1:E1, 0)
MATCH searches for the value in H2 across the column headers (A1:E1) and returns the relative position of the found cell. In our case, the target year (2010) is found in E1, which is 5th in the lookup array. So, the number 5 goes to the col_index_num argument of VLOOKUP:
VLOOKUP(H1, A2:E4, 5, FALSE)
VLOOKUP takes it from there, finds an exact match for its lookup value in A2 and returns a value from the 5th column in the same row, which is the cell E2.
Important note! For the formula to work correctly, table_array (A2:E4) of VLOOKUP and lookup_array of MATCH (A1:E1) must have the same number of columns, otherwise the number passed by MATCH to col_index_num will be incorrect (won't correspond to the column's position in table_array).
XLOOKUP function to look in rows and columns
Recently Microsoft has introduced one more function in Excel that is meant to replace all existing lookup functions such as VLOOKUP, HLOOKUP and INDEX MATCH. Among other things, XLOOKUP can look at the intersection of a specific row and column:
XLOOKUP(vlookup_value, vlookup_column_range, XLOOKUP(hlookup_value, hlookup_row_range, data_array))
For our sample data set, the formula goes as follows:
=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))
Note. The XLOOKUP function is only available in Excel for Microsoft 365, Excel 2021, and Excel for the web.
How this formula works
The formula uses the ability of XLOOKUP to return an entire row or column. The inner function searches for the target year in the header row and returns all the values for that year (in this example, for year 1980). Those values go to the return_array argument of the outer XLOOKUP:
XLOOKUP(H1, A2:A4, {22000;25000;700}))
The outer XLOOKUP function searches for the target animal across the column headers and returns the value in the same position from the return_array.
SUMPRODUCT formula for two-way lookup
The SUMPRODUCT function is like a Swiss knife in Excel – it can do so many things beyond its designated purpose, especially when it comes to evaluating multiple criteria.
To look up two criteria, in rows and columns, use this generic formula:
SUMPRODUCT(vlookup_column_range = vlookup_value) * (hlookup_row_range = hlookup_value), data_array)
To perform a 2-way lookup in our dataset, the formula goes as follows:
=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)
The below syntax will work too:
At the heart of the formula, we compare two lookup values against the row and column headers (the target animal in H1 against all animal names in A2:A4 and the target year in H2 against all years in B1:E1): This results in 2 arrays of TRUE and FALSE values, where TRUE's represent matches: The multiplication operation coerces the TRUE and FALSE values into 1's and 0's and produces a two-dimensional array of 4 columns and 3 rows (rows are separated by semicolons and each column of data by a comma): The SUMPRODUCT functions multiplies the elements of the above array by the items of B2:E4 in the same positions: And because multiplying by zero gives zero, only the item corresponding to 1 in the first array survives: Finally, SUMPRODUCT adds up the elements of the resulting array and returns a value of 2000. Note. If your table has more than one row or/and column headers with the same name, the final array will contain more than one number other than zero, and all those numbers will be added up. As the result, you will get a sum of values that meet both criteria. It is what makes the SUMPRODUCT formula different from INDEX MATCH MATCH and VLOOKUP, which return the first found match.=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)
How this formula works
(A2:A4=H1) * (B1:E1=H2){FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}{0,0,0,0;0,0,0,0;0,1,0,0}{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})
Matrix lookup with named ranges (explicit Intersection)
One more amazingly simple way to do a matrix lookup in Excel is by using named ranges. Here's how:
Part 1: Name columns and rows
The fastest way to name each row and each column in your table is this:
- Select the whole table (A1:E4 in our case).
- On the Formulas tab, in the Defined Names group, click Create from Selection or press the Ctrl + Shift + F3 shortcut.
- In the Create Names from Selection dialog box, select Top row and Left column, and click OK.
This automatically creates names based on the row and column headers. However, there are a couple of caveats:
- If your column and/or rows headers are numbers or contain specific characters that are not allowed in Excel names, the names for such columns and rows won't be created. To see a list of created names, open the Name Manager (Ctrl + F3). If some names are missing, define them manually as explained in How to name a range in Excel.
- If some of your row or column headers contain spaces, the spaces will be replaced with underscores, for example, Polar_bear.
For our sample table, Excel automatically created only the row names. The column names have to be created manually because the column headers are numbers. To overcome this, you can simply preface the numbers with underscores, like _1990.
As the result, we have the following named ranges:
Part 2: Make a matrix lookup formula
To pull a value at the intersection of a given row and column, just type one of the following generic formulas in an empty cell:
=row_name column_name
Or vice versa:
=column_name row_name
For example, to get the population of blue whales in 1990, the formula is as simple as:
=Blue_whale _1990
If someone needs more detailed instructions, the following steps will walk you through the process:
- In a cell where you want the result to appear, type the equality sign (=).
- Start typing the name of the target row, say, Blue_whale. After you've typed a couple of characters, Excel will display all existing names that match your input. Double-click the desired name to enter it in your formula:
- After the row name, type a space, which works as the intersection operator in this case.
- Enter the target column name ( _1990 in our case).
- As soon as both the row and column names are entered, Excel will highlight the corresponding row and column in your table, and you press Enter to complete the formula:
Your matrix lookup is done, and the below screenshot shows the result:
That's how to look up in rows and columns in Excel. I thank you for reading and hope to see you on our blog next week!
Available downloads
2-dimensional lookup sample workbook
You may also be interested in
- VLOOKUP multiple matches with one or more criteria
- How to VLOOKUP across multiple sheets
- Merge Tables Wizard – quick way to Vlookup in Excel
FAQs
INDEX MATCH MATCH in Excel for two-dimensional lookup? ›
Excel allows a user to do a two-dimensional lookup using the INDEX and MATCH functions. The MATCH function returns a row and a column for values in a table, while the INDEX returns a value for row and column.
How do I search a 2D array in Excel? ›In Excel, a 2D array has values along cell rows and columns. To find the location of any particular value in the 2D array, the SUMPRODUCT function should be used.
What is a 2 dimensional lookup in Excel? ›What is a 2D lookup? At 2D lookups, you search for the row and column at the same time. In many cases, you only look for a value in – let's say – column A and return the corresponding value from column B. But there are also many examples, in which you don't know the return column.
Does INDEX match work horizontally? ›INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria.
How do you match two columns in Excel INDEX match? ›The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty. To compare two columns in Excel for differences, replace the equals sign with the non-equality sign (<>).
How do you find the index of a 2D array? ›Two-dimensional (2D) arrays are indexed by two subscripts, one for the row and one for the column. Each element in the 2D array must by the same type, either a primitive type or object type.
Can you do a VLOOKUP with 2 lookup values? ›You can't specify two lookup values in a VLOOKUP formula, so we'll need to use a workaround, which consists of two steps: Step1: Create a separate column where we will create unique lookup_values by merging our two lookup criteria – name and country – for example “MellaThailand“, “MellaNigeria“, etc.
Can you have 2 lookup values in Xlookup? ›NOTE: You can provide Excel more than two LOOKUP_VALUEs and LOOKUP_RANGEs – theoretically an unlimited number. You can also provide multiple RETURN_RANGEs. Try using XLOOKUP in place of VLOOKUP or INDEX-MATCH – you'll be pleasantly surprised how easy and versatile it is to use.
How does a 2D lookup table work? ›The Lookup Table (2D) block computes an approximation of output value with given values of two (2D) inputs and outputs using the specified lookup method.
What are the limitations of INDEX match in Excel? ›The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error.
Can you use Xlookup instead of INDEX match? ›
XLOOKUP just killed VLOOKUP: everything to know about this major new Excel function. Microsoft recently announced the upcoming release of a new function called XLOOKUP. This function will be replacing the widely used VLOOKUP, HLOOKUP and INDEX/MATCH functions to run searches in a table of Excel data.
What is the difference between INDEX Xmatch and INDEX match? ›The 5 key differences between XMATCH and MATCH are: XMATCH defaults to an exact match, while MATCH defaults to an approximate match. XMATCH can find the next larger item or the next smaller item. XMATCH can perform a reverse search (i.e. search from last to first).
How do I match vertical and horizontal data in Excel? ›=MATCH() returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria. For short, this is referred to as just the Index Match function. To see a video tutorial, check out our free Excel Crash Course.
How do you lookup multiple criteria horizontally and vertically in Excel? ›The XLOOKUP function in Excel searches a range or an array for a specified value and returns the related value from another column. It can look up both vertically and horizontally and perform an exact match (default), approximate (closest) match, or wildcard (partial) match.
Why isn t my INDEX match match working? ›If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because: The cell has unexpected characters or hidden spaces. The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.
How do you check if an array is 2D or 1D? ›Look for array. shape: if it comes like (2,) means digit at first place but nothing after after comma,its 1D. Else if it comes like (2,10) means two digits with comma,its 2D.
What is an example of a two-dimensional array? ›We can declare a two-dimensional integer array say 'x' with 10 rows and 20 columns as: Example: int x[10][20]; Note: In this type of declaration, the array is allocated memory in the stack and the size of the array should be known at the compile time i.e. size of the array is fixed.
How do you check if all elements in a 2D array are the same? ›- for (i = 0; i <= Column - 1; i++)
- {
- for (j = 0; j <= Row - 1; j++)
- {
- if (colorArray [i, j] == 4)
- {
- Debug. Log("balanced");
- }
The 2d index supports calculations on a flat, Euclidean plane. The 2d index also supports distance-only calculations on a sphere (i.e. $nearSphere ), but for geometric calculations on a sphere (e.g. $geoWithin ), store data as GeoJSON objects and use a 2dsphere index. A 2d index can reference two fields.
How do you sort a 2d array by index? ›- Step 1 − Create the array called 'arr' of type numbers.
- Step 2 − Iterate through every row of the matrix to sort every row separately.
- Step 3 − Call the sort() method for every row.
- Step 4 − Pass the callback function as a parameter of the sort() method, which takes the two values of the row as a parameter.
What is the first index of a 2d array? ›
The first element in a 2d array is at row 0 and column 0.
What is the difference between VLOOKUP and Xlookup? ›To recap, the key differences between an XLOOKUP and a VLOOKUP are: XLOOKUP can look for values to the left and right of the lookup array, while VLOOKUP is limited to only looking for values to the right of the lookup value column.
Can VLOOKUP handle multiple matching? ›In Excel, VLOOKUP cannot natively return multiple values from multiple matches. Use FILTER to look up all the matches and return the corresponding values. The value that is returned from the formula.
Can you use Xlookup for 2 columns? ›One advantage of the XLOOKUP Function is that it can return multiple consecutive columns at once. To do this, input the range of consecutive columns in the return array, and it will return all the columns. Notice here our return array is two columns (C and D), and thus 2 columns are returned.
Is Xlookup or index match faster? ›Because calculation times for VLOOKUP and INDEX/MATCH are on a similar level, the performance of XLOOKUP compared to INDEX/MATCH doesn't surprise much: XLOOKUP is significantly slower than INDEX/MATCH as well.
How do I use multiple Xlookup matches? ›- Enter the FILTER function.
- Define the first argument, return_array.
- Use an expression as the second argument: lookup_array = lookup_value.
- The formula returns all matching records.
The Lookup Table Dynamic block computes an approximation of a function y = f(x) using xdat and ydat vectors. The lookup method can use interpolation, extrapolation, or the original values of the input. Using the Lookup Table Dynamic block, you can change the table data without stopping the simulation.
What is the difference between lookup table and dimension table? ›Dimension tables, also known as lookup or reference tables, contain the relatively static data in the data warehouse. Dimension tables store the information you normally use to contain queries. Dimension tables are usually textual and descriptive and you can use them as the row headers of the result set.
What is two way lookup? ›Performing a two-way lookup is all about getting a value from a two-dimensional table. That means you can get a value from any cell of a table. And for this, you need to combine the match function with VLOOKUP. As you know, a normal VLOOKUP is only able to return a value by looking into a single column at a time.
What are the disadvantages of INDEX match? ›The main disadvantage of INDEX and MATCH is that it's not well known. Therefore other people working on your workbook might not immediately understand it. Applying the INDEX and MATCH combination is comparatively difficult.
What is the advantage of using INDEX match ()) over VLOOKUP review? ›
One of the biggest issues with using VLOOKUP is that it uses a static column reference. On the other hand, INDEX MATCH uses a dynamic column reference which means it can lead to fewer errors when moving around columns. This is an advantage that becomes more apparent as you create larger and more complex spreadsheets.
How does INDEX match work if there are multiple matches? ›INDEX MATCH with multiple criteria is an 'array formula' created from the INDEX and MATCH functions. The synergies between the INDEX and MATCH functions are that: MATCH searches for a value and returns a location. MATCH feeds the location to the INDEX function.
Can INDEX match do everything VLOOKUP does? ›VLOOKUP will only work if the lookup value is in the first column. VLOOKUP cannot look to its left. However, INDEX MATCH solves this problem as it performs the lookup both horizontally and vertically. So, it doesn't require the lookup value to be in the first column, it can be anywhere.
What replaces VLOOKUP INDEX match? ›By combining the INDEX and MATCH functions, we have a comparable replacement for VLOOKUP. To write the formula combining the two, we use the MATCH function to for the row_num argument. In the example above I used a 4 for the row_num argument for INDEX. We can just replace that with the MATCH formula we wrote.
What is the best use of INDEX match? ›You can use the INDEX and MATCH functions to find a specific value from a table and match it with another value. You can do so by first using INDEX to retrieve a value from the table based on its location and then correlating it with a different value by using MATCH.
Why is XLOOKUP better than INDEX MATCH? ›If backward compatibility is not needed, XLOOKUP is better than regular INDEX and MATCH in several ways: XLOOKUP is simpler, has safe defaults, has built-in error handling, and is very flexible. However, when we compare XLOOKUP to INDEX + XMATCH, the contest is much closer.
Is INDEX match more accurate than VLOOKUP? ›With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP. Additionally, however, you can use a version of the INDEX-MATCH technique to calculate MUCH more quickly than with VLOOKUP.
Can you INDEX match vertical and horizontal? ›Combining the INDEX & MATCH functions allows you to perform lookups by matching values using multiple criteria. In other words, it allows you to disambiguate in cases where there are multiple potential matches for a given value. These lookups can be vertical, horizontal, or both.
Can you do a horizontal INDEX match? ›INDEX/MATCH can work with vertical and horizontal ranges
VLOOKUP can only go through data that is vertical, while INDEX/MATCH can go through data vertically as well horizontally.
Click the cell where you want to add the nested functions. Click the Formulas tab. Click the Lookup & Reference button in the Function Library group. You will start with the INDEX function and nest the MATCH function within it.
Can you do a horizontal lookup in Excel? ›
HLOOKUP in Excel stands for 'Horizontal Lookup'. It is a function that makes Excel search for a certain value in a row (the so called 'table array'), in order to return a value from a different row in the same column.
What is the difference between vertical and horizontal VLOOKUP? ›VLOOKUP or Vertical-LOOKUP and HLOOKUP or Horizontal-LOOKUP are referencing functions in Excel that reference data to match a table array or a group of data and display the output. The difference between these referencing functions is that VLOOKUP references with columns while HLOOKUP references with rows.
How do I do a VLOOKUP with multiple values horizontally? ›To get the multiple corresponding values horizontally, in one row, just make one change in the formula, by replacing "ROW(1:1)" to "COLUMN(A1)", and then copy the formula horizontally in the same row to the right columns, from Cell B11 to H11, in 7 columns (Refer Table 6).
What is wrong with my INDEX match formula? ›Check your cell references
One of the most common problems with index-match is mistakes with static and relative references in the formulas. This will result in incorrect values or error messages when you drag or copy the formulas.
Column A has a list of values which need to be matched with Column B. If matched, the adjacent values in Column C should be returned.
Can you do INDEX match with 3 criteria? ›To perform an INDEX MATCH with multiple criteria in Excel, simply use an ampersand (&) to place multiple references in your lookup value and lookup array inputs in the MATCH formula.
Can you search an array in Excel? ›The array form of the LOOKUP function searches the specified value in the first column or row of the array and retrieves a value from the same position in the last column or row of the array. Where: Lookup_value - a value to search for in an array. Array - a range of cells where you want to search for the lookup value.
Can you do a binary search on a 2D array? ›Since the array is already sorted along the rows as well as columns, thus we can do binary search to locate the exact position of target element and treat the 2D array as a flattened sorted list.
How to do a 2D cell reference in Excel? ›- Select cell J4 and click on it.
- Insert the formula: =INDEX(C4:G8, MATCH(J3, B4:B8, 0), MATCH(J2, C3:G3, 0))
- Press enter.
Another way to see arrays is to use the F9 key. If I carefully select just the range B5:B14, and then press F9, we see the original values. To undo this step, use control + z. Often, you'll want to check the values in an array being passed into a function as an argument.
How do I match an array value in Excel? ›
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
What is the best way to search in array? ›Use filter if you want to find all items in an array that meet a specific condition. Use find if you want to check if that at least one item meets a specific condition. Use includes if you want to check if an array contains a particular value. Use indexOf if you want to find the index of a particular item in an array.
How do you traverse a 2D array? ›The first for loop loops through each row of the 2D array one by one. As the first loop runs through each row, the second (nested) for loop inside the first loop loops through the columns one by one. The nested for loops runs row by row, checking each column within the row before moving on to the next row.
How to get 2D array as input? ›- n_rows= int(input("Number of rows:"))
- n_columns = int(input("Number of columns:"))
- #Define the matrix.
- matrix = [ ]
- print("Enter the entries row-wise:")
- #for user input.
- for i in range(n_rows): # A for loop for row entries.
- a =[ ]
XLOOKUP can figure out either the first or the last worth when different qualities match. Yet, INDEX-MATCH can return the principal esteem that matches. Both support the array formula.
What is a 2d lookup table? ›The two-dimensional Lookup Table block performs a lookup in a user-defined table for the value of a single dependent variable (the block output) using two fields as indexes (the independent variables, block inputs).
How do I create a dynamic array in Excel? ›A dynamic array formula is entered in one cell and completed with a regular Enter keystroke. To complete an old-fashioned array formula, you need to press Ctrl + Shift + Enter. New array formulas spill to many cells automatically. CSE formulas must be copied to a range of cells to return multiple results.
How does Xlookup work in Excel? ›The XLOOKUP function in Excel searches a range or an array for a specified value and returns the related value from another column. It can look up both vertically and horizontally and perform an exact match (default), approximate (closest) match, or wildcard (partial) match.