MATCH Function for Array

[jgaska]

I need to determine the location of a cell within a spreadsheet based on the contents of the cell. My spreadsheet is a calender, divided up into 12 named regions, one for each month. I need to find the row and column of the cell that contains the date (number) specified by some other input cell. For example, if I input "June" into my month input and "12" into my day input, I know how to make it look in my "June" region, but I need to find the location of the 12th within that region. I tried using MATCH, but it would only work for a vector, not an array.

 

[AlphaFrog]

Try using the .Find method.

Example code:

    Dim strMonth As String, iDay As Integer
    
    strMonth = "June"
    iDay = 12
    
    Range(strMonth).Find(iDay, , xlValues, xlWhole, xlByRows, xlNext).Select

 

[gecs]

Presuming in A1 you have "June" and in A2 the date you're looking for, try:

=ADDRESS(SUMPRODUCT((INDIRECT($A$1)=$A$2)*ROW(INDIRECT($A$1))),SUMPRODUCT((INDIRECT($A$1)=$A$2)*COLUMN(INDIRECT($A$1))))