Make Index & Match into UDF or VBA

[Rumpkin]

How can I make this formula either into UDF or a VBA procedure?
INDEX(TOTAL_WAGES,MATCH($F12,Resource_Code_LIST,0),MATCH(INDEX(WAGE_DECISION_DESC_LIST,MATCH(WAGE,WAGE_TYPE,0)),WAGE_NAME,0)))

 

[codeliftsleep]

Adjust the range to whatever you want

public sub myUDF()
    With Sheets("mySheet")
       .Range("A2:A50").Formula = "=[COLOR=#333333]INDEX(TOTAL_WAGES,MATCH($F12,Resource_Code_LIST,0),MATCH(INDEX(WAGE_DECISION_DESC_LIST,MATCH(WAGE,WAGE_TYPE,0)),WAGE_NAM E,0)))"[/COLOR]
 
    End With
End sub

 

[Max1616]

Try this in a module:

Function SpecialLookUp(rng1 As Range, val1 As Variant, rng2 As Range, rng3 As Range, val2 As Variant, rng4 As Range, rng5 As Range)

SpecialLookUp = WorksheetFunction.Index(rng1, WorksheetFunction.Match(val1, rng2, 0), WorksheetFunction.Match(WorksheetFunction.Index(rng3, WorksheetFunction.Match(val2, rng4, 0)), rng5, 0))

End Function

Your formula will look like this:
=SpecialLookUp(TOTAL_WAGES,$F12,Resource_Code_LIST,WAGE_DECISION_DESC_LIST,WAGE,WAGE_TYPE,WAGE_NAME)

The structure of this formula is below:
=SpecialLookUp(rng1,val1,rng2,rng3,val2,rng4,rng5)

Let me know if you have any questions.

 

[Rumpkin]

What can be written if Val1 and or Val2 are either 0 or N/A?
Also can I hide the formula without protecting the sheet?

Thanks!

 

[Max1616]

Those are look up values, so those are 0 or N/A the entire formula will fail (unless there is a look up reference in your ranges for 0 and N/A)

What do you mean by "Hide the formula"? You can always lock the cell where the formula lives, and require a password to edit the cell.

 

[Rumpkin]

Thanks. I'll just write a iferror or if 0 in the formula in the worksheet. I was hoping it could be done in the procedure but could not figure out how to write the iferror in the UDF.
Yeah, sure wish there was a way to hide the formulas without having to protect the sheet
Anyway thanks a million for all your help. I'm needed to post another question regarding a countif formula so maybe if you see it you may know the answer.

 

[Max1616]

You can use this for error handling:

Function SpecialLookUp(rng1 As Range, val1 As Variant, rng2 As Range, rng3 As Range, val2 As Variant, rng4 As Range, rng5 As Range)
SpecialLookUp = 0
On Error GoTo End1
SpecialLookUp = WorksheetFunction.Index(rng1, WorksheetFunction.Match(val1, rng2, 0), WorksheetFunction.Match(WorksheetFunction.Index(rng3, WorksheetFunction.Match(val2, rng4, 0)), rng5, 0))
End1:
End Function

 

[Rumpkin]

Sweet!!

Thank you