我来帮你搞定这个问题，核心问题其实是你现有代码里的数组引用逻辑漏洞，再加上我们可以优化替换逻辑，全程保持内存数组操作（完全不碰单元格循环），既解决替换不生效的问题，又保证效率。

先排查你现有代码没生效的核心原因 #

1. 没有把数据读到内存数组：你写的retval = Intersect(.Range("E:E"), .UsedRange)和mtch = Intersect(.Range("B:D"), .UsedRange)没有加.Value，这导致retval和mtch是Range对象，不是内存里的值数组。后续操作其实是在直接访问单元格，不仅效率低，还可能因为单元格格式、大小写等问题导致判断失效。
2. 嵌套循环的重复赋值：你的双层循环里，如果有多个j满足匹配条件，outArr(i,1)会被多次覆盖，最后只保留最后一个匹配的结果，可能和你预期的第一个匹配不符。
3. 大小写敏感：如果单元格里是Yes/YES而不是全小写的yes，你的IIf(v = "Yes", ...)会判断失败，导致替换不触发。

优化后的高效代码 #

下面的代码全程基于内存数组操作，先批量完成Yes/No的替换，再优化匹配逻辑，彻底解决问题：

Sub IndexMatchFirm1()
    Dim destinationWs As Worksheet
    Set destinationWs = ThisWorkbook.Worksheets("Master")
    Dim destinationLastRow As Long
    destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
    Dim lkpArr As Variant
    ' 把查找值读到内存数组
    lkpArr = destinationWs.Range("A5:A" & destinationLastRow).Value
    
    Dim dataWs As Worksheet
    Set dataWs = ThisWorkbook.Worksheets("MyData")
    Dim retval As Variant
    ' 关键：加.Value把数据读到内存数组，不是Range对象
    retval = Intersect(dataWs.Range("E:E"), dataWs.UsedRange).Value
    Dim mtch As Variant
    mtch = Intersect(dataWs.Range("B:D"), dataWs.UsedRange).Value
    
    ' 第一步：批量处理retval数组，把Yes/No替换为1/0（不区分大小写）
    Dim j As Long
    For j = LBound(retval, 1) To UBound(retval, 1)
        Select Case UCase(CStr(retval(j, 1))) ' 转字符串+大写，避免大小写和类型问题
            Case "YES"
                retval(j, 1) = 1
            Case "NO"
                retval(j, 1) = 0
            ' 其他值保持原样，不需要修改
        End Select
    Next j
    
    Dim outArr As Variant
    ReDim outArr(1 To UBound(lkpArr, 1), 1 To 1)
    Dim i As Long
    Dim foundMatch As Boolean ' 标记是否找到匹配，避免无效循环
    
    For i = 1 To UBound(lkpArr, 1)
        foundMatch = False
        For j = 1 To UBound(mtch, 1)
            ' 先判断是否是FirmA，再匹配查找值，找到就赋值并退出循环
            If mtch(j, 3) = "FirmA" And mtch(j, 1) = lkpArr(i, 1) Then
                outArr(i, 1) = retval(j, 1)
                foundMatch = True
                Exit For ' 找到第一个匹配就停止j循环，大幅提升效率
            End If
        Next j
        ' 可选：如果没找到匹配，可以设置默认值，比如空或0
        ' If Not foundMatch Then outArr(i, 1) = ""
    Next i
    
    ' 把结果一次性写入工作表
    destinationWs.Range("L5").Resize(UBound(outArr, 1), 1).Value = outArr
End Sub

优化亮点说明 #

• 全程内存数组操作：所有数据读取、处理、赋值都在内存里完成，完全不用遍历单元格，效率拉满。
• 提前批量替换：在匹配之前就把所有Yes/No替换成1/0，逻辑更清晰，避免在嵌套循环里反复判断。
• 大小写兼容：用UCase(CStr(...))处理，不管单元格里是Yes/YES/yes都能正确识别。
• 避免无效循环：找到第一个匹配后立即退出内层循环，减少不必要的遍历。
• 修复数组引用问题：所有数组都通过.Value读取值，不再引用Range对象，彻底解决判断失效的问题。

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