Search row and column to find value

[brian robards]

I am trying to write a function that will search a set of rows for the 1st value, then search the columns for a 2nd value. Once the function finds the row and column that correspond to the 2 values it will output the value that has the coordinates of that row and column.

ex:

____1 2 3 4 5 6
a __ A B C D E F
b __ G H I J K L

search row for "b", search column for "5", output "K"

[mrice]

The user defined function below should work

Function TwoDFind(RowValue As String, ColumnValue As String, SearchRange As Range)
TargetColumn = Rows(SearchRange.Row).Find(RowValue, , xlValues, xlWhole).Column
TargetRow = Columns(SearchRange.Column).Find(ColumnValue, , xlValues, xlWhole).Row
TwoDFind = Cells(TargetRow, TargetColumn)
End Function

[MSP77079]

If you want a built-in function for this, I think you will find that the OFFSET function is exactly what you are looking for.

To find the second row, 5th column from an given starting position (for example), the formula is:
=OFFSET(address of first cell in the range, 2-1, 5-1)

So, if I put your grid in a worksheet and have the first entry of your grid in cell A1, then the formula looks like this:
=OFFSET(A1,2-1,5-1)

Which, of course, is easier to read as this:
=OFFSET(A1,1,4)