同一个型号不同供应商查询最新单价为什么不对，求帮忙

忙到飞起

=TAKE(FILTER($F$14:$F$19,($D$14:$D$19=H22)*($E$14:$E$19=I22)),-1)
写的不好

zhangcheng6688

学习一下字典
Sub test()
arr = [c13].CurrentRegion
Set d = CreateObject("scripting.dictionary")
ReDim brr(1 To UBound(arr), 1 To 4)
For i = 2 To UBound(arr)
   If Not d.exists(arr(i, 3)) Then
      Set d(arr(i, 3)) = CreateObject("scripting.dictionary")
   End If
   If Not d(arr(i, 3)).exists(arr(i, 2)) Then
      m = m + 1
      For j = 1 To 3
         brr(m, j) = arr(i, j + 1)
      Next
      brr(m, 4) = arr(i, 1)
      d(arr(i, 3))(arr(i, 2)) = m
   Else
      r = d(arr(i, 3))(arr(i, 2))
      If brr(r, 1) < arr(i, 1) Then
         For j = 1 To 3
            brr(r, j) = arr(i, j + 1)
         Next
         brr(r, 4) = arr(i, 1)
      End If
   End If
Next
[h13].CurrentRegion.Offset(1).ClearContents
[h14].Resize(m, 4) = brr
End Sub

有眼光

何必非要使用excel解决这种问题呢？

你是那块小饼干

=LET(T,FILTER($C$14:$C$19,($D$14:$D$19=H14)*($E$14:$E$19=I14),""),_T,FILTER($F$14:$F$19,($D$14:$D$19=H14)*($E$14:$E$19=I14),""),LOOKUP(K14,SORT(HSTACK(T,_T))))

zhangcheng6688

学习一下字典
Sub zcm()
arr = [c13].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   strkey = arr(i, 2) & arr(i, 3)
   If Not d.exists(strkey) Then
      d(strkey) = i
   Else
      If arr(d(strkey), 1) < arr(i, 1) Then
         d(strkey) = i
      End If
   End If
Next
ReDim brr(1 To d.Count, 1 To UBound(arr, 2))
For Each kk In d.keys
   m = m + 1
   For j = 1 To 3
      brr(m, j) = arr(d(kk), j + 1)
   Next
   brr(m, 4) = arr(d(kk), 1)
Next
[h13].CurrentRegion.Offset(1).ClearContents
[h14].Resize(m, UBound(brr, 2)) = brr
End Sub