Get row number from a match

Excel has a function to locate a value on a range of cells and return the number of the row where the value is found, it’s the MATCH() function. The syntax is like this:

MATCH(lookup_value,lookup_array,match_type)

lookup_value is the value that we want to look for and lookup_array is the range of cells where we want to search. match_type can have 3 values:
1 - finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order;
0 - finds the first value that is exactly equal to lookup_value
-1 - finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.
If match_type is omitted, it will assume to be 1.
Here’s an example. We have the following table:


We want to find the name Mary on the range of A2:A6. So we should use this formula:

=MATCH("Mary",A2:A6,0)

This will return 2 because the name Mary can be found on cell A2.

10 comentários:

Andy said...

Great post! This is a very useful article. You should think about joining the Excel conversation on Facebook at http://www.facebook.com/office

Cheers,
Andy
MSFT Office Outreach Team

WingedOne said...

As a clarification, MATCH returns a relative reference in the look-up array, not as an absolute reference to the worksheet cell. In this example, MATCH does return a "2" because "Mary" is the second entry in the array, but not to the absolute location of "Mary", which is in cell A3.

To return an absolute row, the easiest way is to use the ROW function in conjunction with MATCH and reference the array:

=MATCH("Mary",A2:D6,0)+ROW(A2:D6)-1

Two items about the above formula:
-It compensates for arrays not starting out on A1.
-It uses the not-standard use of the ROW function by entering it as a regular (non-array) formula to return the upper row of the array.

Anonymous said...

Great post, and great addition, WingedOne. Thanks.

Anonymous said...

Great post. It return the first row in which the value appears. But how do I find muliple row numbers?

I want to search an entire column for the value "1", and want excel to return me all the row numbers in which this occurs.

Anyone help?

Vasant Shah said...

Great posts. Now I want to go one step further. I want to get value from column C for the row from MATCH function.

Anonymous said...

Vasant Shah, I'm not sure if this is the Kosher way to do it but it can be done using the INDIRECT function. ex: =INDIRECT("A" & C1, TRUE)

This function will return the value of Column A at the row number contained in cell C1.

Nico Durand said...

Vasant, this would be a perfect use for a VLOOKUP function:

VLOOKUP("Mary",A2:D6,1,FALSE)

Nico

Ramarao said...

This is super good. Thanks.

But instead of the value of a cell, if I want to navigate to the row that contains the loopup or matched cell, is there any easy way to do it?

Ramarao said...

I figured. This following syntax in a Macro, works for my purpose. Thanks.

Application.Goto Reference:="INDIRECT(""E""& R1C8,TRUE)"

In this example, I have the formula for returning the row number in cell H1 (R1C8) as follows:

= match (sheet!cell, array, 2)

If this value returns, say 10, then my above go to command takes me to cell "E10" when Macro is run, which is what I needed.

kanwarjeet Singh Chadha said...

What if you had a list of names in column A and scores in column B. We can find the smallest five or largest five scores use the small or large function but how can we find the adjacent name. Match function or lookup fails when you have two names with the same score? Mary 22, John 22, Jack 23, Eric 12, Scot 13. Try this data for example and find the largest 3 scores and adjacent names. You will see that score 22 reveals Mary twice and not John and Mary. Any help would be appreciated. I tried using index, match and lookup but could not resolve it. I think a conditional IF would be needed but not sure how to use it.