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.

## 12 comentários:

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

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.

Great post, and great addition, WingedOne. Thanks.

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?

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

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.

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

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

Nico

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?

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.

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.

kanwarjeet Singh Chadha - were you able to resolve your problem? I have the same issue and am trying to find the best way to fix it.

I think anonymnous question is not yet answered. I too have the same question as his'.. anyone know how to answer our questions:

"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."

Thanks!

Post a Comment