1、首先打开Microsoft Office Excel 2007，新建文档并保存文件名《Excel怎样查找表格纵横向两值A、B值相应值 .xlsm》（演示文件，下面代码复制到能运行宏的工作簿都可以）如图。
2、然后按下快捷键ALT+F11打开VBE（宏）编辑界面，然后点菜单栏【插入】下拉中列表中点【模块（M）】如图。
3、然后插入了一个模块1，在代码框中复制如下代码：Function YXCRCB(ByVal y As Range, ByVal x As Range, ByVal cb As Range) '2020-10-5 22:15:22 Dim i As Long, r1 As Long, c1 As Long, k1 As Long, k2 As Long, n1 As Long, n2 As Long, z1, z2, x1, x2 Dim m1, m2, y1, y2, xy1, xy2 r1 = cb.Rows.Count c1 = cb.Columns.Count If y.Value < cb.Cells(2, 1).Value Or y.Value > cb.Cells(r1, 1).Value Or x < cb.Cells(1, 2).Value Or x > cb.Cells(1, c1).Value Then YXCRCB = "查值超出表范围，请检查纵横值！" Else k1 = 0 For i = 1 To r1 If y.Value = cb.Cells(i, 1).Value Then k1 = i Exit For End If Next i k2 = 0 For i = 2 To c1 If x.Value = cb.Cells(1, i).Value Then k2 = i Exit For End If Next i If k1 > 0 And k2 > 0 Then YXCRCB = cb.Cells(k1, k2).Value ElseIf k1 = 0 And k2 > 0 Then n1 = 0 For i = 2 To r1 - 1 If y.Value > cb.Cells(i, 1).Value And y.Value < cb.Cells(i + 1, 1).Value Then n1 = i Exit For End If Next i y1 = cb.Cells(n1, 1).Value y2 = cb.Cells(n1 + 1, 1).Value m1 = cb.Cells(n1, k2).Value m2 = cb.Cells(n1 + 1, k2).Value YXCRCB = (y.Value - y1) * (m2 - m1) / (y2 - y1) + m1 ElseIf k1 > 0 And k2 = 0 Then n2 = 0 For i = 2 To c1 - 1 If x.Value > cb.Cells(1, i).Value And x.Value < cb.Cells(1, i + 1).Value Then n2 = i Exit For End If Next i x1 = cb.Cells(1, n2).Value x2 = cb.Cells(1, n2 + 1).Value z1 = cb.Cells(k1, n2).Value z2 = cb.Cells(k1, n2 + 1).Value YXCRCB = (x.Value - x1) * (z2 - z1) / (x2 - x1) + z1 Else n1 = 0 For i = 2 To r1 - 1 If y.Value > cb.Cells(i, 1).Value And y.Value < cb.Cells(i + 1, 1).Value Then n1 = i Exit For End If Next i n2 = 0 For i = 2 To c1 - 1 If x.Value > cb.Cells(1, i).Value And x.Value < cb.Cells(1, i + 1).Value Then n2 = i Exit For End If Next i y1 = cb.Cells(n1, 1).Value y2 = cb.Cells(n1 + 1, 1).Value m1 = cb.Cells(n1, n2).Value m2 = cb.Cells(n1 + 1, n2).Value z1 = cb.Cells(n1, n2 + 1).Value z2 = cb.Cells(n1 + 1, n2 + 1).Value xy1 = (y.Value - y1) * (m2 - m1) / (y2 - y1) + m1 xy2 = (y.Value - y1) * (z2 - z1) / (y2 - y1) + z1 x1 = cb.Cells(1, n2).Value x2 = cb.Cells(1, n2 + 1).Value YXCRCB = (x.Value - x1) * (xy2 - xy1) / (x2 - x1) + xy1 End If End IfEnd Function
4、以上操作动态过程如下：
5、回到工作表窗口，输入公式如下：K4： =YXCRCB(I4,J4,$A$3:$G$15) 结果：3.06K5： =YXCRCB(I5,J5,$A$3:$G$15) 结果：0.83K6： =YXCRCB(I6,J6,$A$3:$G$15) 结果：0.86K7： =YXCRCB(I7,J7,$A$3:$G$15) 结果：1.09K8： =YXCRCB(I8,J8,$A$3:$G$15) 结果：0.82K9： =YXCRCB(I9,J9,$A$3:$G$15) 结果：0.6196K10： =YXCRCB(I10,J10,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！K11： =YXCRCB(I11,J11,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！K12： =YXCRCB(I12,J12,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！K13： =YXCRCB(I13,J13,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！
6、如果觉得这篇经验帮到了您，请点击下方的 “投票点赞" 或者“收藏”支持我！还有疑问的话可以点击下方的 “我有疑问”，谢谢啦！
