vlookup函数如何返回符合两个条件的值
上周三有群友询问如何使用 EXCLE VLOOKUP比对查寻两表中符合两条件的值。都知道vlookup一般只能做单条件查寻,具体用法=vlookup(比对值,查寻区域,所取值列,【0,1】)。
下面就罗列几种运用EXCEL函数返回符合两个条件的值。
日期 | 姓名 | 评级 | 分数 | 6月1日 | 王二 | 优秀 | 400 | 6月1日 | 周六 | 良好 | 350 | 6月1日 | 李武 | 及格 | 300 | 6月1日 | 周斌 | 较差 | 250 | 6月1日 | 吴三 | 优秀 | 400 | 6月2日 | 张三 | 良好 | 350 | 6月2日 | 周斌 | 及格 | 300 | 6月2日 | 周六 | 良好 | 350 | 6月2日 | 李武 | 优秀 | 400 | 6月2日 | 吴三 | 良好 | 350 | ------->表“VLA”
日期 | 姓名 | VLOOKUP() | SUMPRODUCT() | SUM() | INDEX() | INDEX() | OFFSET() | OFFSET() | 6月1日 | 王二 | 优秀 | 400 | 400 | 400 | 优秀 | 400 | 优秀 | 6月2日 | 李武 | 优秀 | 400 | 400 | 400 | 优秀 | 400 | 优秀 | 6月2日 | 周六 | 良好 | 350 | 350 | 350 | 良好 | 350 | 良好 | 6月2日 | 李武 | 优秀 | 400 | 400 | 400 | 优秀 | 400 | 优秀 | 6月2日 | 吴三 | 良好 | 350 | 350 | 350 | 良好 | 350 | 良好 | 6月1日 | 李武 | 及格 | 300 | 300 | 300 | 及格 | 300 | 及格 | 6月1日 | 周斌 | 较差 | 250 | 250 | 250 | 较差 | 250 | 较差 | 6月2日 | 张三 | 良好 | 350 | 350 | 350 | 良好 | 350 | 良好 | 6月2日 | 周斌 | 及格 | 300 | 300 | 300 | 及格 | 300 | 及格 | | | 数组函数 | 非数组 | 数组 | 数组 | 数组 | 数组 | 数组 | --------->表"VLB"
效果如上表所示,具体公式如下:
公式1=VLOOKUP(A2&B2,IF({1,0},VLA!$A$2:$A$11&VLA!$B$2:$B$11,VLA!$C$2:$C$11),2,0)
数组公式输入完按ctrl+shift+enter
公式2=SUMPRODUCT((VLA!$A$2:$A$11=A2)*(VLA!$B$2:$B$11=B2)*VLA!$D$2:$D$11)
当所取值区域为数值时可用此函数,文本则不行
公式3=SUM((VLA!$A$2:$A$11=A2)*(VLA!$B$2:$B$11=B2)*VLA!$D$2:$D$11)
当所取值区域为数值时可用此,数组公式输入完按ctrl+shift+enter
公式4=INDEX(VLA!$A$2:$D$11,MATCH(A2&B2,VLA!$A$2:$A$11&VLA!$B$2:$B$11,0),4)
数组公式输入完按ctrl+shift+enter
公式5=OFFSET(VLA!$A$1,MATCH(A2&B2,VLA!$A$2:$A$11&VLA!$B$2:$B$11,0),3)
数组公式输入完按ctrl+shift+enter
|