在Excel中如何利用公式进行双向双列查找

Question

如题所述

Answer 1

双向查找
双向查找就是用公式从横向和纵向两个方向在区域中查找数据，例如下图是某公司某月在一些城市的产品销售数量表，现在要用公式来查找出某城市某产品的销售数量。假如数据区域为B2:E6，需要查找的城市名称和商品名称分别在B9、B10单元格中。
可以用两种方法来实现双向查找：
方法一：INDEX、VLOOKUP或HLOOKUP与MATCH函数的组合公式
=INDEX(B2:E6,MATCH(B9,A2:A6,0),MATCH(B10,B1:E1,0))
或：
=VLOOKUP(B9, A1:E6, MATCH(B10, A1:E1, 0), 0)
或：
=HLOOKUP(C1,A1:E6,MATCH(B9,A1:A6,0),0)
方法二：使用区域交集
1.选择包含行标题和列标题的区域A1:E6，在Excel 2003中单击菜单“插入→名称→指定”，弹出“指定名称”对话框。如果是Excel 2007，则在功能区中单击“公式”选项卡，在“定义的名称”组中，单击“根据所选内容创建”按钮。
单击“确定”，Excel用每行和每列的标题来定义10个区域名称，如“袜子”为B2:B6。
2.输入公式
=拖鞋 青岛
“拖鞋”和“青岛”之间有一空格，公式返回两个区域的交集，即查找出“青岛”的“拖鞋”销售量。
双列双向查找
如果销售表中的第一列还包括月份，我们可以根据“月份”列和“城市”列来查找某个产品的销售量，用下面的数组公式。
假如要查找的月份、城市和产品名称分别在I3、I4、I5单元格中。在单元格中输入下面的公式，公式输入完毕后按Ctrl+Shift+Enter结束：
=INDEX(C2:F16,MATCH(I3&I4,A2:A16&B2:B16,0),MATCH(I5,C1:F1,0))
或：
=HLOOKUP(I5,A1:F16,MATCH(I3&I4,A1:A16&B1:B16,0),0)
说明：公式将两列条件用“&”组合起来，然后用MATCH函数查找其位置，最后用INDEX函数或HLOOKUP函数返回查找结果