Index-match with multiple criteria in worksheetfunction

[dionysus_83]

Hi all,

Well, I`m stuck, I couldn`t find adequate answer in forums. Here is the code:

matchRECORD0 = Application.WorksheetFunction.Index(Sheets("TEMPDB").Range("D" & lrSJIMPORT2 + 1 & ":D" & lrCHIMPORT2), _
Application.WorksheetFunction.Match(Sheets("STATIONS").Cells(RARSTAT2, 2) & Sheets("STATIONS").Cells(1, cARTIST), _
Sheets("TEMPDB").Range("C" & lrSJIMPORT2 + 1 & ":C" & lrCHIMPORT2) & Sheets("TEMPDB").Range("A" & lrSJIMPORT2 + 1 & ":A" & lrCHIMPORT2)))

Entire code is quite large but I receive error on this part! I know it should be set in array but I don`t know how to do that, so question is:

How to make this worksheetfunction as array?

Thnx

 

[CharlesChuckieCharles]

You should Check out the UNION function, Putting all your sheet ranges into one UNION then use the MATCH against the UNION

 

[dionysus_83]

You should Check out the UNION function, Putting all your sheet ranges into one UNION then use the MATCH against the UNION

Will MATCH function work properly then? I have multiple criteria against multiple criteria ranges?

 

[Jonmo1]

I would add helper columns on both sheets to concatenate the ranges.
Then do your matching on those columns.

And instead of doing
application.worksheetfunction.Index(application.worksheetfunction.match

First, drop the worksheet function, and just use application.match
And assign the result of the match to a variable.
Test the variable for error
Then use built in vba syntax to do the indexing..

Dim MyVariable
MyVariable = Application.Match(Sheets("STATIONS").Cells(ConcatenatedColumn, 2),Sheets("TEMPDB").Range("E" & lrSJIMPORT2 + 1 & ":E" & lrCHIMPORT2),0)
'E is the concatenated column on TEMPDB Sheet.
If Not IsError(MyVariable) Then
    matchRECORD0 = Sheets("TEMPDB").Range("D" & MyVariable + lrSJIMPORT2)
End If

 

[dionysus_83]

I would add helper columns on both sheets to concatenate the ranges.
Then do your matching on those columns.

And instead of doing
application.worksheetfunction.Index(application.worksheetfunction.match

First, drop the worksheet function, and just use application.match
And assign the result of the match to a variable.
Test the variable for error
Then use built in vba syntax to do the indexing..

Dim MyVariable
MyVariable = Application.Match(Sheets("STATIONS").Cells(ConcatenatedColumn, 2),Sheets("TEMPDB").Range("E" & lrSJIMPORT2 + 1 & ":E" & lrCHIMPORT2),0)
'E is the concatenated column on TEMPDB Sheet.
If Not IsError(MyVariable) Then
    matchRECORD0 = Sheets("TEMPDB").Range("D" & MyVariable + lrSJIMPORT2)
End If

Excellent suggestion, and I considered that as last chance solution because rest of code is quite large and interacts with different parts of that same table. I didnt think too much in advance so came to this problem. I would like to avoid if possible to use concatenated column solution as I will have ton of work to change rest of code too. But still, thnx a lot as this is a solution to my problem.

 

[Jonmo1]

You're welcome.

That's your choice of course. But I'd pose this question..
Which time is more valueable, Time spent designing/writing the code/project, or the Time spent Executing/Using the code/project ?

 

[steve the fish]

You could try like this:

x = Sheets("TEMPDB").Range("A" & lrSJIMPORT2 + 1 & ":D" & lrCHIMPORT2)
For i = LBound(x, 1) To UBound(x, 1)
    If LCase(x(i, 3)) = LCase(Sheets("STATIONS").Cells(RARSTAT2, 2)) Then
        If LCase(x(i, 1)) = LCase(Sheets("STATIONS").Cells(1, cARTIST)) Then
            matchRECORD0 = x(i, 4)
            Exit For
        End If
    End If
Next

 

[dionysus_83]

Well said @Jonmo1. I will go trough rest of my code to change what is neccessary and use your suggestion.

Thnx a lot