Make Index & Match into UDF or VBA

Rumpkin

Board Regular
Joined
Sep 24, 2016
Messages
54
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)))
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

codeliftsleep

Board Regular
Joined
Apr 14, 2017
Messages
103
Adjust the range to whatever you want

Code:
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

Active Member
Joined
Nov 25, 2015
Messages
385
Try this in a module:
Code:
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

Board Regular
Joined
Sep 24, 2016
Messages
54
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

Active Member
Joined
Nov 25, 2015
Messages
385

ADVERTISEMENT

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

Board Regular
Joined
Sep 24, 2016
Messages
54
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

Active Member
Joined
Nov 25, 2015
Messages
385
You can use this for error handling:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top