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.

## 14 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!

Hi Jayson,

late comment but I have been just bumped into this annoying stuff now.

I have delved down everywhere that Google suggested, even only remotely referencing to our topic here.

I got to the painful conclusion that it won't be done with one easy step to get the absolute reference of the cell.

What I did: I changed the range using variables so when the value searched for is found in the range, the variables would change and in the next search, they seek the value only in the re-set range.

So far so good, but I always have to add the starting row number to get the absolute row number in the worksheet... not so cosy I' say.

Here it is - not all details would catch your attention, but it will give a clear view I hope.

In short: I have column A in which the following pattern repeats around 130 times:

1

2

4

8

9

9

9

I seek number 8 in each pattern.

Then I use the following Do Until loop:

Sub test()

Dim Range3 As Range

Dim StartRow As Long

Dim EndRow As Long

Dim SearchValue As String

SearchValue = 8 'I always seek number 8 in the column that I define as range below

ID = 1

StartRow = 1

'below we define the loop. I use the last row to set the condition to stop looping around:

EndRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Do Until StartRow > EndRow

Set Range3 = Sheets("Sheet1").Range(Cells(StartRow, 1), Cells(EndRow, 1)) 'This is a one-column range set in which we search

Range3.Select 'you can deactivate this - its purpose is just to demonstrate the range in debug mode

On Error Resume Next

a = Application.WorksheetFunction.Match(SearchValue, Range3, 0) + (StartRow - 1) 'correction is added here, as formula uses relative reference only

'if after the last reset of Range3, the value is not found, it would return an error message.

'this Select Case routine will handle it and close the loop.

Select Case Err.Number

Case 1004

Exit Do

End Select

Sheets("Sheet1").Cells(a, 2) = "Number 8 found"

StartRow = a + 1 'here we add the row number in which the value was found, plus one row to re-set the range for new search in loop

Loop

End Sub

Copy the pattern in column A several times, and copy/paste my macro, and run/debug it.

THT, Laszlo

Hi Jayson,

sorry, please ignore below row:

ID = 1

it was left in the code accidentally.

Laszlo

Post a Comment