1

Excel 2013 Enterprise

In my worksheet I have a nested If statement that I would like to have in a macro. Problem is that when I record it, VBA screws it up and makes it unusable (see code below). I use a function for the MySht because the sheet names are to be determined (a macro changes them after a txt file is uploaded.). MySht function:

Function MySht(ByVal sht As Integer)
  Application.Volatile
  MySht = Sheets(sht).Name
End Function

From:

=IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(1) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(1) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(1) & "'!C:C")=B3)*(INDIRECT("'" & MySht(1) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(1) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(2) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(2) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(2) & "'!C:C")=B3)*(INDIRECT("'" & MySht(2) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(2) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(3) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(3) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(3) & "'!C:C")=B3)*(INDIRECT("'" & MySht(3) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(3) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(4) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(4) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(4) & "'!C:C")=B3)*(INDIRECT("'" & MySht(4) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(4) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(5) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(5) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(5) & "'!C:C")=B3)*(INDIRECT("'" & MySht(5) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(5) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(6) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(6) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(6) & "'!C:C")=B3)*(INDIRECT("'" & MySht(6) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(6) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(7) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(7) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(7) & "'!C:C")=B3)*(INDIRECT("'" & MySht(7) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(7) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(8) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(8) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(8) & "'!C:C")=B3)*(INDIRECT("'" & MySht(8) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(8) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(9) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(9) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(9) & "'!C:C")=B3)*(INDIRECT("'" & MySht(9) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(9) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(10) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(10) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(10) & "'!C:C")=B3)*(INDIRECT("'" & MySht(10) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(10) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(11) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(11) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(11) & "'!C:C")=B3)*(INDIRECT("'" & MySht(11) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(11) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(12) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(12) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(12) & "'!C:C")=B3)*(INDIRECT("'" & MySht(12) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(12) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(13) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(13) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(13) & "'!C:C")=B3)*(INDIRECT("'" & MySht(13) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(13) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(14) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(14) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(14) & "'!C:C")=B3)*(INDIRECT("'" & MySht(14) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(14) & "'!C:C"))-ROW($A$2)+1))); "nope"))))))))))))))

To: 

"=IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(1) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(1) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(1) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(1) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(1) & ""'!C:C""))-ROW(R2C1)+1)))," & Chr(10) & "IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(2) & ""'!C:C""),0)" & _
"X(INDIRECT(""'"" & MySht(2) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(2) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(2) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(2) & ""'!C:C""))-ROW(R2C1)+1)))," & Chr(10) & "IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(3) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(3) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MyS" & _
"" '!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(3) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(3) & ""'!C:C""))-ROW(R2C1)+1))),
"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(4) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(4) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(4) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(4) & ""'!D:D"")=R[1]C[-1])*("& _
"ECT(""'"" & MySht(4) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(5) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(5) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(5) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(5) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(5) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R"& _
"INDIRECT(""'"" & MySht(6) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(6) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(6) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(6) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(6) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(7) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht"& _
"!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(7) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(7) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(7) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(8) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(8) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(8) & ""'!C:C"")=R[1]C[-2])*(IN"& _
"'"" & MySht(8) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(8) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(9) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(9) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(9) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(9) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(9) & ""& _
")-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(10) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(10) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(10) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(10) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(10) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & My"& _
" ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(11) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(11) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(11) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(11) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(12) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(12) & ""'!E:E""),SUMP"& _
"NDIRECT(""'"" & MySht(12) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(12) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(12) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(13) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(13) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(13) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & "& _
" & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(13) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(14) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(14) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(14) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(14) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(14) & ""'!C:C"""& _
"C1)+1))), ""nope""))))))))))))))"

I tried to alter my original formula to match something VBA would like but no success: 

"=IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(1) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(1) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(1) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(1) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(1) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(2) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(2) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(2) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(2) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(2) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(3) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(3) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(3) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(3) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(3) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(4) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(4) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(4) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(4) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(4) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(5) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(5) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(5) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(5) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(5) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(6) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(6) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(6) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(6) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(6) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(7) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(7) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(7) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(7) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(7) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(8) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(8) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(8) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(8) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(8) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(9) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(9) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(9) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(9) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(9) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(10) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(10) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(10) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(10) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(10) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(11) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(11) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(11) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(11) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(11) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(12) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(12) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(12) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(12) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(12) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(13) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(13) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(13) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(13) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(13) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(14) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(14) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(14) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(14) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(14) & ""'!C:C""))-ROW($A$2)+1))), ""nope"" ))))))))))))))"

What can I change so Excel and VBA will accept my formula?

Improve this question
5
  • 1
    I would build the condition up from scratch, using a function that does everything from ISERROR onward and takes a sheet as argument. That should make the code a lot more readable, especially since you can comment everything nicely in the function, even if you use worksheetfunctions. Commented Jun 10, 2016 at 10:33
  • @arcadeprecinct (cool name)If I understand you correct, change the condition by altering everything after the iserror with application.worksheetfunction(the conditions of the if) and build up my code like that? Commented Jun 10, 2016 at 10:56
  • 1
    I would avoid using worksheet functions when you can just as well use vba. It's hard to say which way is best without knowing what your data looks like (plus I'm not very good with worksheet functions tbh) Commented Jun 10, 2016 at 11:26
  • I am a novice at VBA, i've tried a vba IF, Else If statement but it failed me. but you would advice to use the vba equivalent of the excel functions? Commented Jun 10, 2016 at 11:32
  • 1
    You don't have to avoid the worksheet functions, sometimes it's just more convenient to write Sheets(1).Column(3) instead of ""'"" & MySht(1) & ""'!C:C"". It depends on the occasion though. I avoid them mainly because I never used excel the "common" way and thus only know the vba functions :D. Also I actually had compatibility issues with formulas between different language versions so I try to stick to the core vba. Commented Jun 10, 2016 at 11:47

2 Answers 2

Reset to default
1

Here is a partial answer to help you structure your code:

First write a function that checks the condition for you (a function makes sense because the condition is the same for each sheet.

Private Function checkCondition(sht As Worksheet) As Boolean
    'check the condition here
End Function

Then call that function for each sheet. To avoid so many nested If statements, you can put them inside a loop:

Dim conditionMet As Boolean 'this is only so it stops checking the rest of the sheets once it finds one that doesn't fulfill the condition
Dim counter As Long

counter = 1
conditionMet = True

While counter <= 14 And conditionMet
    'it will exit the loop if the condition is not met for a sheet
    conditionMet = checkCondition(Sheet(counter))
Wend

If conditionMet Then
    'now do your stuff
Else
    'or something different
End If
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

If I got you right, then you want a function like this:

Public Function ultraMatch(find_what_1 As String, in_column_1 As String, find_what_2 As Variant, in_column_2 As String, first_sheet As Long, last_sheet As Long, return_column As String) As String
Dim i As Long, j As Long, rng1_val, rng2_val
For i = first_sheet To last_sheet
  With Sheets(i)
    rng1_val = Intersect(.Columns(in_column_1), .UsedRange).Value
    If IsNumeric(Application.Match(find_what_1, rng1_val, 0)) Then
      rng2_val = Intersect(.Columns(in_column_2), .UsedRange).Value
      For j = Application.Match(find_what_1, rng1_val, 0) To UBound(rng1_val)
        If rng1_val(j, 1) = find_what_1 And rng2_val(j, 1) = find_what_2 Then
          ultraMatch = .Cells(j, return_column).Value
        End If
      Next
    End If
  End With
Next
End Function

And in your worksheet simply use something like this:

=ultraMatch(B2,"C",D2,"D",1,14,"J")

or alternatively (less obvious to my eye):

=ultraMatch(B2,3,D2,4,1,14,10)

This way, the whole function also is not volatile.

If you have any questions, just ask ;)

Improve this answer

1 Comment

Wow, thanks for the responses. I'm more familiar with the Excel formulas than vba. but I have been bumping on incompatibility of excel versions. I will try the function and loop and report back (If I have a solution I will let you guys knowl)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.