MATCH formula with external lookup_array data

Solved/Closed
Albert17 - Mar 2, 2010 at 07:29 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 2, 2010 at 08:22 AM
Hello,

I am trying to write a formula using the MATCH function =MATCH("x",r1:r10,0) to find the first instance of x in a column. However .... I need to be able to change the lookup_array data without altering the formula. For most formulas you can use INDIRECT or enter a reference to another cell to pull the data into the formula. The MATCH function does not evaluate references, and INDIRECT will not work because the reference is a range and not a single cell.

Any ideas of how to solve this?

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 2, 2010 at 08:22 AM
Why not go for UDF

Some thing like

Function myMatch(searchString As String, rangeString As String) as variant

Set myRange = Range(rangeString )

myMatch = Application.WorksheetFunction.Match(searchString , myRange, 0)

End Function
1