1638人阅读
& & 其实我本来对EXCEL是一窍不通的，直到我舅舅给了一串大概1500多行的药品供应目录让帮他我查找。当他告诉他们平时都是花1~2天人工填写查找的时候，我当时就惊呆了，心想EXCEL设计者肯定已经哭晕在厕所，大家竟然都不用便捷的公式来完成，于是乎我就开始上手了。&&
&& 首先应该明确需求，拥有一个包含药品名称、规格、供应商、价格的数据库A，表A如下。从表A中我们可以发现，同一药品，同一规格具有多个供应商，所以这一定是一个多结果查找。
& &需要根据医院需求的药品清单，补全一下清单表格B，表B如下。从表B中我们可以发现，B列所代表的通用名并不是该行数据的KEY，KEY是序列号，B列、C列、D列只不过是查找条件之一而已，需要根绝名称、剂型、规格等多个条件来查找企业信息、报价信息等。所以这是一个多条件查找过程。
& & 以上我们明确了本次任务是完成多条件多结果查找，首先应该建立一新的工作环境(Work Space)，简单的说就是新建一个EXCEL文件，并把两张表导入Sheet1、Sheet2分页中去，这样方便引用，是个良好的习惯。导入方法如下，右击左下角的Sheet1分页，选择移动或复制
& & 为了解决问题，我们先从简单的方面入手，多条件多结果查找首先需要完成查找的基本功能，查找所用的函数一般是VLOOKUP，意思是在一列(vertical)中找到(Look up)符合条件的第一个值并返回，语法规格为
& & & & & & & & & & & & =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
譬如=VLOOKUP($O4,Sheet3!$H:$I,2,0)，其中$O4表示查找内容是O4单元框的内容，查找范围是Sheet3!$H:$I，即表二的H到I列，2表示查找到相应行之后，返回O列右边第二列的内容，0是查找类型。符号$表示固定，因为随着公式下拉，数字4会依次增加，变为O4，O5，O5，而随着右拉列号会依次增加，变为O4，P4等，符号$的左右就保证右拉时列号O不会改变。
多条件查找&
& & 当查找结果为多个时，我们首先的想法可能是按照C语言，使用&符号，当然，可是肯定行不通的。正统方法是利用数组，这种方法先不理它...
& & 另一种方法更直接一些，建立一个辅助列，将多条件化为单条件，设需要满足的条件是E3、F3，我们只需要建立辅助列，令他的值为=E3&F3，满足该辅助列的单一条件就能实现满足多条件的需求。如下图所示例如H列，令H2=A2&C2，建立辅助列，对H列查找就能满足多条件查找的需求。
多结果查找
& & & 但是适用VLOOKUP函数只能返回第一个结果，不符合多结果查找的要求。为了解决多结果查找，我们需要适用INDEX & SMALL &IF&组合函数。通过IF条件语句，遍历找到所有符合条件的行，并返回该行的序号
& & & & & & & & & & & &=IF(Sheet2!$E2:$E1335=Sheet1!$F3,ROW(Sheet2!$E2:$E)
& &&其中Sheet2:$E2：$E1335=Sheet1!$F3是判断条件，在表二的E列中，找到等于表一中F3单元格内容的行，返回行号ROW(Sheet2!$E2:$E1335),否则返回4^8=65535数值。找到所有符合条件的结果后，将他们排序，并按照行输出(右拉)
&=INDEX(Sheet2!$G:$G,SMALL(IF(Sheet2!$E2:$E1335=Sheet1!$F3,ROW(Sheet2!$E2:$E),COLUMN(A1)))&&&
& & &其中SMALL仅仅对所有结果从小到大排序，而INDEX则将结果一一输出，按列输出COLUMN，按行输出则是ROW。先做个小实验，如下图。在A列中查找C1=1的值，D1输入公式=INDEX($B:$B,SMALL(IF($A$1:$A$1000=$C1,ROW($1:$),COLUMN(A1)))&&&，输入后按下CRTL+ENTER(使结果为数组)，再右拉可以得到结果。
& & & 将这种方法运用到药品企业价格查找的实例中去，如下图所示，这里只右拉了3格，最多显示了3家企业，O列为条件查找辅助列，P~R为相应的企业，S~V为相应的价格。至此我们完成了多条件和多结果的查找。收尾工作需要把辅助列删除，并且把得到的结果赋值-选择性粘贴-数值，以防移动后出现没有reference的情况。
多行多列变一列
& & &开开心心地把这个表格交给我舅舅，准备听几句夸奖的话，结果他说不行啊，这不同企业和价格必须在一列显示啊，这样才方便比对价格作出选择。于是我就愁了，要怎么才能把这种多行多列的数据变为一列呢？解决办法当然是有的。使用OFFSET函数
=OFFSET(($U$3),INT((ROW(A1)-1)/4),MOD(ROW(A1)-1,4))
& & & &其中4代表了多行多列中的列个数，我最后每种药品最多列出4家供应商，对于使用者请按自己情况改变该数字，$U$3是多行多列的最左上角数值。运用到我们得实例中去，其中I~L是多行多列矩阵，在F3中输入公式并下拉，得到一列。
每行增加行
& & & &因为一种药品对应了4家供应商，所以每种药品必须占4行，才能和上诉的一列正确对应上，这时我们需要使原有的每行间隔三行。这种苦力活当然不可能一个个增加，必须是批量操作的。
& & & &这种方法可以按照下图的辅助列排序方法，最后一步用的是快捷键排序，没显示出来，这种方法适合每行空多行。
& & &我用的方法是如下建立辅助列，定位空值后插入行，当然还是前两种比较好。
最后应用到实例中，得到结果
& & & 把之前多行多列变一列得到的4个为一组的单列复制到后面一行，就能得到最终的结果
删除空白行
& & &许多没有供给公司和药品序号的行就是完全的空白行，这些影响观看，需要删除，建立辅助列，判断A和F同时为空时，赋值#N/A，CRTL+F替换#N/A为空值，F5定位空值，删除空值，可以达到要求，最终结果如下
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：5950次
排名：千里之外vlookup函数返回多个查找结果_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
vlookup函数返回多个查找结果
上传于|0|0|暂无简介
阅读已结束，如果下载本文需要使用0下载券
想免费下载更多文档？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩1页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢excel条件查询返回结果_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
excel条件查询返回结果
上传于|0|0|文档简介
&&图文并茂、通俗易懂、实用、适用
阅读已结束，如果下载本文需要使用5下载券
想免费下载本文？
定制HR最喜欢的简历
你可能喜欢(window.slotbydup=window.slotbydup || []).push({
id: '3284507',
container: s,
size: '0,0',
display: 'inlay-fix'
excel vba中Vlookup返回多个值 自定义函数
VlookUP函数只能返回第一个值，无法返回多个值
下面这个自定义函数可以解决这个问题
可以这么理解，是Vlookup函数返回多个值，将其对应的所有值都列出来
Function LookS(rng As Range, rg As Range, i As Byte, ii As Integer)
'第1参数为查找的单元格，第2参数是查找范围，第3参数为返回的列，第4参数为返回的第几个值
'第1参数和第2参数都要锁定行
Dim arr, a%, x%
For a = 1 To UBound(arr, 1)
If arr(a, 1) = rng Then
If x = ii Then LookS = arr(a, i): Exit For
If a & UBound(arr, 1) Then LookS = &&
End Function
excel vba中Vlookup返回多个值 自定义函数
excel vba中Vlookup返回多个值 自定义函数
标签(Tag):
------分隔线----------------------------
------分隔线----------------------------查看: 11181|回复: 26
【已解决】Vlookup函数返回多个符合条件值的问题
阅读权限10
在线时间 小时
我把问题在附件中做了说明，希望有大师帮助解惑啊~~
我是做人事的，经常遇到的问题：
假如现要制一份名单A（缺工资 数值），另外有一份工资表B
序号& &姓名&&性别& &部门& && &&&工资
1& && && & 王君& &女& & 生产部& && &
2& && && & 陈兵& &男& & 财务部
3& && && & 王君& &女& & 物资部
序号& &姓名& &&&部门& && &&&工资
1& && && & 王君& &&&物资部& && &3500
2& && && & 陈兵& &&&财务部& && &2900
3& && && & 王君& &&&生产部& && &3700
如何 用 Vlookup 函数 在A表中 引出 B表工资项~~~& &（主要问题：王君同名）？
谢谢！！！！各位！！！
[ 本帖最后由 ciokevin 于
14:17 编辑 ]
16:31 上传
点击文件名下载附件
3.35 KB, 下载次数: 265
阅读权限95
在线时间 小时
& & & & & & & &
Vlookup函数返回多个符合条件值的问题,這樣的情況必須要再加一個識別碼,總之要區分開...[em01]
16:34 上传
点击文件名下载附件
3.7 KB, 下载次数: 622
阅读权限50
在线时间 小时
& & & & & & & &
lookup是不是更适合些？为什么楼主的附件我打开后不能自动计算了
阅读权限100
在线时间 小时
& & & & & & & &
D1=INDEX(表1!$F$2:$F$6,MATCH(B2&C2,表1!$B$2:$B$6&表2!$C$2:$C$6,))
17:00 上传
点击文件名下载附件
4.04 KB, 下载次数: 325
阅读权限95
在线时间 小时
IF({1,0},區域A,區域B)這种使用方法常在VLOOKUP等函數中出現,我們都知道,VLOOKUP只能從左往右查找數據,不能返向查找,也不能跨多區域查找.舉個例子:要查找的值在B例,要得出的值在A例,正常你用VLOOKUP就沒有辦法了.這時必須用IF({1,0}將兩個區域的順序調換一下,=IF({1,0},B,A},這樣就形成了一個b列在前a列在後的數組,才能用vlookup(查找值,{b,a},2,)來查找了...不知說明白了沒有?![em01]
阅读权限10
在线时间 小时
2楼 xiongkehua2008& & 很好地达到了要求~~~ 很感谢~~
3楼 dengjq2000& &出现的问题 就是我要问的问题~~ 我试了 对应错误~
很感谢~~~你们的回答~~
还请2楼 能不能就 =VLOOKUP(B2&C2,IF({1,0},表1!$B$2:$B$6&表1!$C$2:$C$6,表1!$F$2:$F$6),2,)& &做个详解啊~~ 谢谢了~~~~~
B2&C2&&好理解&&查找 连接起来的字符，后面{1,0}& &&&2&&怎么理解啊
阅读权限100
在线时间 小时
D2=LOOKUP(2,1/((表1!$B$2:$B$6=B2)*(表1!$C$2:$C$6=C2)),表1!$F$2:$F$6)&&往下拉
阅读权限100
在线时间 小时
回复 5楼 ciokevin 的帖子
对不起！由于昨天急于下班，没有仔细校对，这个试试
D2=INDEX(表1!F:F,MATCH(B2,OFFSET(表1!$B$1,MATCH(C2,表1!C:C,)-1,,99),)+MATCH(C2,表1!C:C,)-1,)
阅读权限20
在线时间 小时
原帖由 xiongkehua2008 于
16:34 发表
Vlookup函数返回多个符合条件值的问题,這樣的情況必須要再加一個識別碼,總之要區分開...[em01]
牛人，就是这样的
阅读权限10
在线时间 小时
回复 8楼 dengjq2000 的帖子
试验了~· 很强大~~~~ 呵呵 可以了 ~~~ 谢谢啊~~~！！
最新热点 /1
ExcelHome每周都有线上直播公开课，
国内一流讲师真身分享，高手贴身答疑，
赶不上直播还能看录像，
关键居然是免费的！
厚木哥们都已经这么努力了，
你还好意思说学不好Office。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师}