哈啰，大家好
在之前的教学影片中
我们曾谈过 VLOOKUP 这个函数
它可以根据你所提供的资料
在表格的「最左栏」进行搜寻
然后再将符合条件的内容回传给使用者
虽然 VLOOKUP 概念简单
函数的设定也很方便
但是 VLOOKUP 也有一个很大的缺点
就是今天如果你要搜寻的栏位
并不是在表格的最左侧
或是你的表格采用了横向排列的话
那么 VLOOKUP 就完全英雄无用武之地了
那么我们要如何解决因为表格设计
导致 VLOOKUP 无法运作的问题呢？
首先，如果你遇到的是一个横向表格
而要查询的资料列也洽巧是第一列的话
那么你可以使用 VLOOKUP 的另一个版本
叫做 HLOOKUP
这两个函数的差异
仅在于它们的查询方向不同
而函数的结构是完全一样的
例如今天我想要查询
编号为 105 的员工业绩
那么我可以输入 HLOOKUP 之后
点击员工编号的栏位
来取得要查询的数值
接着指定查询的范围
也就是底下的资料表格
最后，由于「业绩」是位于这个表格的第三列
因此我输入一个 3
HLOOKUP 就可以帮我们查到这位员工的业绩
不过，HLOOKUP 和 VLOOKUP
都有一个相同的困境
就是它们都只能进行「单向」的查询
而无法从条件符合的栏位中
同时进行左、右
和上、下的查询
因此，这时就是 Excel 中最知名的函数搭档
INDEX 和 MATCH 大展身手的时候了
我们同样先来看一下
这两个函数的基本用法
然后再来研究它们是如何相辅相成
成为 Excel 老手最为青睐的函数组合
首先，关于 INDEX 函数
它最基础的型式
就是我们可以在一栏或一列的范围中
指定一个你要的顺位
INDEX 就可以传回这个位置的资料
举个例子
如果我在建立 INDEX 函数时
将资料范围指定在 B 栏
顺位为第 3 个
那么 INDEX 就会回传「小美」这个名字
同理，如果我将资料范围修改为第二列
那么第二列的第三个人就是「小轩」
OK，除了从「单栏」或「单列」回传资料之外
INDEX 也可以将范围
扩展为一个多栏的表格
这时 INDEX 的函数结构
除了得指定资料的范围之外
还必须指定你要查找的「列数」
和「栏数」
我同样以左边的座位表为例
如果这次我将 INDEX 的范围
设为全班同学
然后将列数指定为 3
栏数指定为 4
那么 INDEX 就会回传「小玲」的名字
OK，那么 MATCH 函数
又是如何运作的呢？
MATCH 的概念乍看之下
有点像是 INDEX 的逆向操作
在使用 INDEX 时
我们是先指定一个顺位
然后 INDEX 会以这个顺位传回姓名
而 MATCH 函数刚好相反
我们得先丢给 MATCH 函数一个姓名
然后 MATCH 再告诉我们
这个人是排在第几个顺位
好，我将 MATCH 的函数结构写在旁边
我们一起来实作看看
MATCH 的第一个引数
是「要查找的对象」
譬如我输入「阿福」
接着我们要指定查找的「范围」
而这边特别要注意的是
MATCH 函数的范围
必须是「单栏」或「单列」
而不能像 INDEX 可以含括整个表格
而不能像 INDEX 可以含括整个表格
在此我将范围设为 B4:E4
然后输入一个逗号
这时，我们会进入到
MATCH 的第三个引数
也就是在比对人名时
是要「完全符合」
还是求取一个「近似值」
我依照 Excel 的提示
输入一个 0
代表我所输入的姓名
必须和座位表中的姓名完全相同
才算是符合条件
如此 MATCH 函数就会告诉我们
「阿福」在 B4:E4 这一列中
他的座位是排在第二个顺位
当然，我们也可以试着将查询范围
更改为 C 栏
那么 MATCH 函数
就会告诉我们「阿福」是排在
C 栏中的第三个顺位
那么在什么情况之下
MATCH 函数会采用「近似值」的比对呢？
「分数区间」就是一个很常见的例子
譬如今天我想要查询 88 分
是落在那一个区间的话
我可以将 MATCH 函数的查询对象
指定到「分数」栏位
搜寻的范围则是在 B 栏
由于我们不可能在 B 栏中
找到同样为「88 分」的数值
因此我们可以将 MATCH 的比对方式设为 1
也就是查询这个分数
所能够达到的「最高」区间
如此 MATCH 函数
就会回传「4 」这个顺位值了
有趣的是
我们可以继续利用这个顺位
进一步找出 88 分所隶属的等第
而这里能够协助我们查出等第的函数
恰恰是我们刚才所学到的 INDEX
我们只要将 INDEX 的查询范围
设在「等第」栏
至于要传回第几列
我们可以直接撷取
刚才 MATCH 函数的查询结果
就可以得到「甲」这个成绩
事实上，我们更可以将这两个公式
进一步简化为一个
如此我们就可以省略掉中间的储存格
此时，如果我们输入其它的分数
INDEX 也都可以查出正确的等第喔
OK，最后我们来整理一下
今天所学到的技巧
来设计一个查询表格
我打算藉由业务员姓名的输入
来查询他是在那一个分公司？
目前的业绩如何？
以及他的考绩等第
而这里我们所遇到的挑战
在于「业务员」这个栏位
并不是在表格的最左栏
因此对于只能向右查询的 VLOOKUP 来说
它在这里是无计可施的
我们只能将这个任务
交付给今天的主角 INDEX 和 MATCH 函数
OK，我首先替这个查询表
设计一个下拉选单
来加速使用者的查询流程
我切换到「资料」标签
点击「资料验证」
接着我将储存格的资料类型设为「清单」
并且在底下的「来源」栏位中
输入「分公司」
逗点
「业绩」
逗点
「考绩」
也就是左边表格的标题栏
再按下「确定」来关闭对话框
如此，我们就可以从下拉选单中
选取要查询的栏位
此外，为了让使用者省去输入业务员姓名的麻烦
我在业务员的栏位
同样执行「资料验证」
来建立一个下拉选单
而这个选单的内容
则是所有业务员的姓名
再按下「确定」
那么 INDEX 和 MATCH
在这里要如何分工呢？
基本上 INDEX 所扮演的角色
是负责将资料传回到查询表
但是要传回那一列
那一栏则必须仰赖 MATCH 函数的帮忙
举例来说
如果今天我要搜寻的业务员叫做「戴育如」
那么我可以在建立 MATCH 函数时
将查找的对象指定到姓名栏
查找的范围则是 D 栏
比对的方式是完全符合
如此我们就可以得知
「戴育如」所在的列数是第 5 列
同样地，要传回那一栏的资料
则是由 H3 的栏标题所决定
因此我同样利用 MATCH 函数
来查找「分公司」在标题列中
是位于第几个顺位
而这里所得到的答案是 1
也就是「分公司」在这个表格中是第一个栏位
而当「列数」和「栏数」的资讯一应俱全后
我们就可以在查询表内建立 INDEX 函数
资料的范围是左侧的表格
而接下来的两个引数
我们可以将刚才完成的 MATCH 函数
直接照抄回来
如此，当我们指定了其它的业务员
以及想要查看的栏位
EXCEL 都可以立即传回正确的查询结果喔
OK，所以下次当你遇到
VLOOKUP 无法处理的表格时
你不妨试试看 INDEX 和 MATCH 这两个函数
那今天我们就说到这里
我们同样下回再见，拜拜