VLOOKUP靠边站，这才是Excel中最牛的查找方法？（建议收藏）

公众号新闻

2023-06-17 00:06

作者：小爽

编辑：竺兰、萌萌

大家好，我是在研究数据结构的小爽~

每当公司有大型的活动或者会议时，都会选择到大展厅进行演示。小李作为公司人力资源部的一名员工，需要对展厅的座位情况提前进行安排。

这不，他就设计了一张下图这样的表格。表中标明了每个人对应的座位情况（假设姓名不重复）。

不料，当他沾沾自喜地欣赏这张表的时候，领导突然发消息，问：有个叫危平的客户坐在哪里？

他从表格的左上角开始，用目光扫视表格。过了两三秒，回复领导，坐在 1 排 7 号的位置。

没想到领导又问，还有那个我们部门的小爽安排坐哪了？

他又开始从左上角一个一个地进行扫描查找，还没等他找到，领导突然走到他旁边，一脸不可思议地望着他，拍了一下他的肩膀，说道：

我们人力资源部用 Excel 的频率很高的呀。这样，你不要一个个进行查找，试着用 Excel，借助工具进行查找，晚点再找我汇报一下。

小李开始思索。

用函数查找位置，一般是 Match 函数。它可以查找一行或者一列的相对位置。但是，查找区域不是一行或者一列，那想要既同时查找行，又同时查找列。Match 好像……做不到啊 !

于是，他向我求助。

接下来，就和小李一起来看看我是怎么做到的吧！

数组判断

在 B2 单元格输入如下公式 :

=INDIRECT(  TEXT(    MAX((D5:S20=A2)*(ROW(6:21)/1%+COLUMN(D:S))),    "R0C00"    ),   FALSE   )

PS : 除了 Office365、Office2021 之外，其他版本需要在输入公式后，按数组三键【Ctrl+Shift+Enter】返回结果。

下面简单解释一下这个函数公式：

👉将位置区域的行列标范围，转换成对应数值

返回区域 : D6:S21

D6:S21 为 1 排 1 号到 6 排 16 号形成的区域。

6 到 21 为这个区域的行标范围。

D 到 S 列为这个区域的列范围。

判断区域中行列标范围对应的数值。

利用 Row 函数构造这个区域的行标数值范围。

利用 Column 函数，构造列标数值范围。

👉将行列汇总成一个区域，里面每个数值包含对应返回区域的行列信息

区域 ①

=(ROW(6:21))/1%+COLUMN(D:S)

数据的行列信息我们已经得到了。那么后面就需要将行列数汇总起来。

由于列数最多是两位。所以我们可以将行数扩大 100 倍然后加上列数。

扩大行数（*100，或者/1% 都表示扩大 100 倍）

如下图：

加上列数。

这里我们就得到一个区域，里面每个数值中都包含返回区域对应的行列信息。

比如 604，就是第 6 行第 4 列(D 列)，也就是 D6。

上面的区域信息，就对应下图中的数据位置。

👉查找值跟查找区域直接判断

区域② :

=(D5:S20=A2)

如下图 D5:S20 为对应的查找区域。这个区域跟返回区域 D6:S21，区域大小一致。

我们通过单元格区域，直接进行判断。

查找区域=查找值，如果返回结果为 true 的话，证明查找值就在对应查找区域的某个位置上。如下图 :

D5:S20 为姓名区域，也就是查找区域。

A2 单元格为查找值，也就是小爽

D5:S20=A2 判断小爽在哪。

👉区域① * 区域②，聚合后得到查找值对应的行列数值

如下图，两者利用*号进行判断。公式：

=((ROW(6:21))/1%+COLUMN(D:S))*(D5:S20=A2)

利用 max/min/sum 将区域变成为一个值。（这里用了 max 函数）

结果为 2113，表示小爽的位置在第 21 行第 13 列的位置。

👉构造单元格，返回对应位置的值

利用 Text 函数，将行列数(2113)变成单元格地址格式 R21C13。

PS : 单元格地址有两种表达方式 : A1 方式和 R1C1 方式。由于 A1 方式中字母构造比较复杂，所以这里我们用 R1C1 方式进行构造。

利用 Indirect 函数，返回对应单元格区域，第二参数为 false，表示我们采取了 R1C1 方式。

这时结果为 6 排 10 号，也就是查找值对应的位置。

到这里，函数的方法就解释完了。

小李在小爽电脑旁边，若有所思。

我看着他，笑着跟他说，我再教你一招，在 Excel 中就有一个查找功能，你知道快捷键是什么嘛?

小李摸了摸头，这个我知道，是【CTRL+F】。

VBA 扩展

针对位置的查找，Excel 中就有一个查找功能。

如下图，通过查找功能，我们一下子就能找到小爽在哪里。

而且小爽单元格下面的信息，就是我们想要的位置内容。

这样是不是就比重头一个个肉眼查找快多了鸭。我边说边望向小李。

小李红着脸，点了点头。

如果不想每次需要查找的时候都要【CTRL+F】，这时 VBA 就派上用场了。

PS : Excel 文件需保存为 xlsm 格式。

由于查找后，返回的是一个 Range 对象，所以利用 Offset 函数向下偏移 1 个单元格，就是我们想要的数据结果，顺着这个思路，我们写了一个 Function 函数过程。

VBA 代码如下。

findwz 函数有两个参数，第一个参数是查找区域，第二个参数是查找值。

Public Function findwz(Rng As Range, rngCell As Range) As String    Dim rngResult As Range    With Rng        Set rngCell = .Find(What:=rngCell.Value, After:=.Cells(1), _                LookIn:=xlValues, LookAt:=xlWhole)        If Not rngCell Is Nothing Then             Set rngResult = rngCell.Offset(1, 0)             findwz = rngResult.Value        Else             findwz = "不存在"        End If    End With    Set rngCell = Nothing    Set rngResult = Nothing    Set Rng = NothingEnd Function