Formula to find match in two-dimensional range

two-dimensional lookup excel
index match multiple criteria rows and columns
sumproduct 2 dimensional lookup
vlookup
index match match
matchif
excel lookup intersection of row and column
excel find value in table

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 A1:C3 for Chad and return C2 or 2,3. How can I accomplish this using Excel formulas? (I'll actually end up applying this to Google Sheets).

Thanks!


Assuming you're using Excel 2007 and above.

You will need a helper column. If your table looks like in your example, in cell D1 write:

=IFERROR(MATCH($E$1,$A1:$C1,0),0)

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:

=IF(MAX($D:$D)=0,NA(),MATCH(MAX($D:$D),$D:$D,1)&","&MAX($D:$D))

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