Formula to find match in two-dimensional range
I need a formula that will look up a value in a 2-dimensional range and return the coordinates or cell address of the matching cell. For example:
R A B C 1 John Matt Pete 2 Sara Bret Chad 3 Lila Maya Cami
I want to search the range
Chad and return
2,3. How can I accomplish this using Excel formulas? (I'll actually end up applying this to Google Sheets).
Assuming you're using Excel 2007 and above.
You will need a helper column. If your table looks like in your example, in cell
And drag it down. Then in cell
E1 write your search value (
"Chad" for instance). Then you have your search result in cell
E2 with this formula:
Perform a Two Dimensional Lookup in Excel, How do you do a two dimensional lookup in Excel? 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))
MATCH function, 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. =MATCH (H7,B1:E1,0) H7 = Bronze – the lookup_value. B1:E1 = list of medals across the columns – the lookup_array 0 = an exact match – the match_type
INDEX MATCH MATCH and other ways to do two-dimensional , (25,A1:A3,0) returns the number 2, because 25 is the second item in the range. Match Range: Select the range that captures the other value by changing range reference ("B4:D4") in the VBA code to any range in the worksheet, that doesn't conflict with the formula. Explanation about how to apply a two dimensional lookup using a combination of the Excel VLOOKUP and MATCH functions
INDEX MATCH MATCH in Excel: 2 dimension lookup, A matrix lookup formula with named ranges. Your matrix That's how to look up in rows and columns in Excel. To apply a two dimensional lookup we can use a combination of the Excel INDEX and MATCH functions. FORMULAS. =INDEX (data_range,MATCH (row_lookup_value,row_lookup_range,0),MATCH (column_lookup_value,column_lookup_range,0)) ARGUMENTS. data_range: A table of data from which to lookup the value.
How to perform a two-dimensional lookup, The MATCH function returns a row and a column for values in a table, while the a two-dimensional lookup in Excel. Figure 1. The final result of the formula array – a range of cells where we want to get a data; row_num – a number of a row The second MATCH function has lookup_value J2, lookup_array C3:G38 and match_type 0. The result of this function is the column_num parameter of the INDEX function. The array parameter is the range C4:G8. To apply the formula, we need to follow these steps: Select cell J4 and click on it; Insert the formula: =INDEX(C4:G8, MATCH(J3, B4:B8, 0
INDEX and MATCH in Excel, For a 2-dimensional lookup the most powerful, understandable and fastest function Create a dynamic range; Array formula to match multiple criteria in rows Find out more about the MATCH function in this article: MATCH Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more excel match value(get row) in two-dimensional array