如何用VBA替代vlookup+match?并解决它们的pain point

Mengkelyu

假设我们有一张表，需要对其对row和column分别做lookup找到交叉值。举个例子说

	1	2	3
cat	0.12	0.22	0.13
dog	0.23	0.14	0.15

取cat和3的交叉值，也就是0.13。这里如果用excel function来做的话就是vlookup和match的组合。这里用VBA实现如下特性。

Excel里面最大的一个痛点是会区别数字的两个格式，数字格式和String格式。
举个例子说，如果我vlookup的值是3，但是是字符3，也就是'3，就无法和数字3匹配。VBA写的这个lookup可以完美解决这个问题。
既可以在Excel里面直接当作function来使用，也可以在别的VBA程序中调用
速度相对较快

Option Explicit

Function lookup_2way(ra As Range, lookup_col As String, lookup_row As String, vague As Integer):
Dim i As Integer
Dim j As Integer
Dim counter1 As Integer
Dim counter2 As Integer

Dim status As Integer

status = 0
Dim expense_table() As Variant
expense_table = ra.Value

If vague = 0 Then
    counter1 = 0
    For i = 1 To UBound(expense_table, 1)
        counter1 = counter1 + 1
        If TypeName(expense_table(i, 1)) <> "String" Then expense_table(i, 1) = Str(expense_table(i, 1))
        If expense_table(i, 1) = lookup_row Then
            status = 1
            Exit For
        End If
    Next i
    counter2 = 0
    
    If status = 1 Then
        For i = 1 To UBound(expense_table, 2)
            counter2 = counter2 + 1
            'Debug.Print (TypeName(expense_table(1, i)))
             If TypeName(expense_table(1, i)) <> "String" Then expense_table(1, i) = Str(expense_table(1, i))
            'why use trim: to exclude the space before or after string
            If Trim(expense_table(1, i)) = Trim(lookup_col) Then
                status = 2
                Exit For
            End If
        Next i
    End If
    
    If status = 2 Then lookup_2way = expense_table(counter1, counter2)
Else
    lookup_row = CDbl(lookup_row)
    counter1 = 0
    For i = 1 To UBound(expense_table, 1)
        counter1 = counter1 + 1
        If expense_table(i, 1) > lookup_row Then
        status = 1
            Exit For
        End If
    Next i
    counter2 = 0
    
    For i = 1 To UBound(expense_table, 2)
        counter2 = counter2 + 1
        If TypeName(expense_table(1, i)) <> "String" Then expense_table(1, i) = Str(expense_table(1, i))
        If expense_table(1, i) = lookup_col Then
                status = 2
                Exit For
            End If
    Next i
    
    lookup_2way = expense_table(counter1, counter2)
End If
End Function

Mengkelyu

先解释调用的参数，ra是要lookup的表，lookup_col是要找的列的名字，lookup_row是要找的行的名字，vague是一个是否要模糊查找列的参数，0是精确查找，非0是模糊查找。
我写的VBA里面有几个特点

读入range对象后，完全用数组操作，速度较快
把读入的数字都转换为string格式，好做匹配
虽然读入参数用的lookup_row必须是string的格式，但是测试了一下在Excel里面当作Excel function用的时候可以读入数字格式的数，并且结果正确
在VBA使用的时候直接改变输入参数的格式即可
模糊查找列是仿照vlookup的模糊查找功能，还没有经过足够测试

Mengkelyu

@wb: 其实有个比较简单的解决方案，在你的例子里，搜索值‘3 是个字符，解决办法就是用match或者vlookup的时候在后面家上 +0 强制转换成数字就可以了
例如 MATCH(C7+0,C2:E2,0)

反过来如果是数字需要搜索字符，用 &'' 空字符强制转换