Excel多条件匹配(多条件、匹配值对应多个符合值两种情况)

实例

图1

图2

图2中根据车号、司机姓名从图1中匹配出指令单号,第一,多条件匹配,要同时满足车号与司机姓名;第二,一个司机对应多条指令单号,所以要将所有符合的指令单号匹配出来。

方法

本文采用VBA代码,自定义adaptation函数
adaptation(车号范围,指令单号范围,司机姓名范围,需匹配司机姓名,需匹配车号)

Function adaptation(vehicle As Range, order As Range, driver As Range, dri As String, veh As String)
Dim t As String
'如果车号与指令单号的区域大小不同,就显示“错误”
If vehicle.Rows.Count <> order.Rows.Count Then adaptation = "错误": Exit Function
'在区域车号里循环
For i = 1 To vehicle.Rows.Count
'如果在vehicle中找到与veh相同的值,并且在driver中找到与dri相同的值,就把同一行中的order的内容提取出来,存入变量t中。
If vehicle.Cells(i, 1) = veh And driver.Cells(i, 1) = dri Then t = t & " " & order.Cells(i, 1)
Next
'将变量的值赋给自定义函数
adaptation = t
End Function