如何将Excel中的MATCH与INDEX函数转换为自定义VBA函数？

Got it, let's turn that MATCH/INDEX combo into a clean VBA custom function that behaves exactly like you need. Here's a step-by-step solution:

Step 1: Create the Custom VBA Function #

First, open the VBA Editor by pressing Alt + F11. Right-click your workbook in the Project Explorer pane, select Insert > Module, then paste this code into the module window:

Function GetDataFromRange(matchKey As Variant, targetHeader As String, dataRange As Range) As Variant
    Dim colIndex As Variant
    Dim rowIndex As Variant
    
    ' Find the column number of the target header in the data range's first row
    colIndex = Application.Match(targetHeader, dataRange.Rows(1), 0)
    
    ' Find the row number of the match key in the data range's first column
    rowIndex = Application.Match(matchKey, dataRange.Columns(1), 0)
    
    ' Handle cases where either the header or match key isn't found
    If IsError(colIndex) Or IsError(rowIndex) Then
        GetDataFromRange = CVErr(xlErrNA) ' Return #N/A to match Excel's native behavior
    Else
        ' Pull the value from the intersecting cell
        GetDataFromRange = dataRange.Cells(rowIndex, colIndex).Value
    End If
End Function

Step 2: Use the Function in Your Worksheet #

Now you can use this custom function just like any built-in Excel function. For your specific setup:

• In cell B4, enter: =GetDataFromRange(A4, $A$3, $D$8:$G$12)
• Drag the fill handle down to B5 to apply it to your second match key

How It Works #

This function replicates your original MATCH/INDEX logic perfectly:

• matchKey: The value you're searching for in the first column of DataRange (A4/A5 in your example)
• targetHeader: The column header you want to retrieve data from (A3, which switches between "Shop_Name" and "Status")
• dataRange: Your source data area (D8:G12)

When you change A3 to "Status", B4 and B5 will automatically update to "Same" and "New" just like your original formula setup. If a match key or header can't be found, it returns #N/A—consistent with Excel's native functions.

Quick Tips #

• Save your workbook as a .xlsm or .xlsb file (macro-enabled formats) to keep the custom function intact
• The function is case-insensitive, just like Excel's default MATCH behavior

内容的提问来源于stack exchange，提问作者learnerben