Home > Forum Home > Planning and Managing Projects > Locate row based on cell data Share

Locate row based on cell data

Excel Help for Locate Row Based On Cell Data in Planning and Managing Projects

Forum TopicPost Reply Login

Locate Row Based On Cell Data

Rate this:
(3/5 from 1 vote)
ConfusedCell A1 contains variable. Macro must look up a column and match A1 data to 
value in the column and select that row which contains the data so that
I can update things in that row. Clear as mud in a beer bottle?

e.g. If cell A1 is 10 and in column B there is a series of numbers 1-20 & 10 is in row 14, then select that row in column B so that can copy variables to certain cells the row.
 Posted by on
Replies - Displaying 1 to 5 of 5Order Replies By: Most Recent | Chronological | Highest Rated
Rate this:
(3/5 from 1 vote)
This can easily be done with a macro; however it is also possible to do without a macro at all.

Taking your example, the following spreadsheet shows the solution using formulas.

Excel Spreadsheet:

  A B
1  4  1
2  =MATCH(A1,B1:B5,0)  2
3  =ADDRESS(A2,2)  3
4  =HYPERLINK("#"&A3,"Go to row")  4
5    5
Cell A1 is the value for which row that we desire to navigate to. The values to match are in column B (only 5 rows are used here for illustration purposes.

Cell A2 finds the index number of column B by matching the value in cell A1 using the MATCH function.  The value is 4 as the number 4 is the fourth row in column B.  The last parameter is set to 0 to find the first match. Setting it to 1 will find the last match.

Cell A3 then gets the cell address of the matching row number and column B (2) using the ADDRESS function. The value is $B$4.

Finally, cell A4 creates a hyperlink to the address using the HYPERLINK function.  This creates a link in the cell and when clicked goes to the address value in cell A3 - in this case B4.  Note that when creating this formula,  we need to add the "#" symbol to indicate that the destination is within the same worksheet (in the same way as defining an HTML bookmark link).

Combining all of the formulas, we can simply create the dynamic cell hyperlink as one formula in cell A2 as follows:

=HYPERLINK("#"&ADDRESS(MATCH(A1,B1:B5,0),2),"Go to row")

 Excel Business Forums Administrator
 Posted by on
Rate this:
(3/5 from 1 vote)
Was able to use this to locate row, very good, but I then need to copy data
to that row.

HYPERLINK("#"&ADDRESS(MATCH(A1,B1:B5,0),2),"Go to row")
 tried many combinations of this to create a macro without sucess

 Posted by on
Rate this:
(3/5 from 1 vote)
If you can give an example of where and in what format the source data is in and the destination details (e.g. use the "Insert an Excel Spreadsheet" icon in the forum posting form), then we can look at posting a generic macro that would accomplish this.
The idea would be to make it dynamic enough to be used for multiple purposes.

 Excel Business Forums Administrator
 Posted by on
ConfusedHow to find data of a particular row and column in excel?
 keylogger detector
 Posted by on
HappyYou can get the data value of a specified row and column number using the INDIRECT and ADDRESS functions:
=INDIRECT(ADDRESS(row number,column number))

 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Locate row based on cell data in the