Excel匹配多个条件 [英] Excel Match multiple criteria

查看:235
本文介绍了Excel匹配多个条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

有人可以帮我把这个2条件匹配功能转换成4个条件匹配功能吗?这一个起作用,但只是开始:

  = INDEX(range1,MATCH(1,(A19 = range2)* B19 = range3),0))

我还想在上面的公式中进行第三和第四场比赛,这两个是OR选项。我以为根据这个工作版本可能有效,但是它没有:

  = INDEX(range1,MATCH(1 ,(A19 = range2)*(B19 = range3)*(OR(C19 = range4,D19 = range5)),0))

我一直在尝试使用AND命令,我的初始版本的第一个代码是:

  = INDEX(range1,MATCH(1,AND(A19 = range2,B19 = range3),0))

在CTRL + ALT + ENTER输入后总是返回#N / A,所以我明白了MATCH或AND(或者我猜这两个)的理解,这显然是个问题,



第一个例子按照预期工作得很好,但不幸的是我不知道为什么我不能很好地适应它。也许我太累了,我的脑袋里已经用尽了Excel公式的特殊方式,但是我已经阅读并重新阅读了他们的帮助文件,但对我来说并没有什么意义。



与以往一样,任何帮助都将不胜感激。



谢谢,



Joe

解决方案

我只是在这里猜测,但这是否有效?

  = INDEX(range1,MATCH(1,(A19 = range2)*(B19 = range3)*(((C19 = range4)+ = range5))> 0),0))


Could someone help me turn this 2 Criteria match function into a 4 criteria match function please? This one works, but is only the start:

=INDEX(range1,MATCH(1,(A19=range2)*(B19=range3),0))

I also want a third and fourth match in the above formula, with those two being an OR option. I thought based upon the working version that this might work, but it doesn't:

=INDEX(range1,MATCH(1,(A19=range2)*(B19=range3)*(OR(C19=range4,D19=range5)),0))

I've been trying to use AND commands, my initial version of the first code above being this:

=INDEX(range1,MATCH(1,AND(A19=range2,B19=range3),0))

It always returns #N/A after CTRL+ALT+ENTER is entered though, so it's obviously an issue with my understanding of either MATCH or AND (or both I guess),

The first example works EXACTLY as intended, but unfortunately I don't know why and I can't work it out well enough to adapt it. Maybe I'm too tired and have run out of space in my head for the peculiar way in which Excel formulas work, but I've read and re-read the help files for them and still it doesn't make sense to me.

Any help would be greatly appreciated, as always.

Thanks,

Joe

解决方案

I'm just guessing here, but would this work?

=INDEX(range1,MATCH(1,(A19=range2)*(B19=range3)*(((C19 = range4)+(D19 = range5))>0),0))

这篇关于Excel匹配多个条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆