如何在excel 2007和更高版本中查找具有多个条件的值 [英] How to lookup value with multiple criteria in excel 2007 and newer
问题描述
我想在多个条件的Excel表中查找一个值。我已经尝试过:
= INDEX($ D $ 2:$ D $ 10,MATCH(1,($ B $ 2:$ B $ 10 = A13)*($ C $ 2:$ C $ 10 = A13),0))
但是它不起作用。
我使用这个:
= LOOKUP(2,1 /(预约$ A:!$ a = $ A20)/(预约$ B:$ B = F $ 5)/(预约$ D:$ D = 0),保留$ C! :$ C)
...哪些工作正常,但速度非常慢。
有没有使用索引与多个标准?
通常使用单一标准所有你需要做的是输入:
INDEX($ D $ 2:$ D $ 10,MATCH(A13,$ B $ 2 :$ B $ 10,0))
然后点击输入,你是
但是,由于您在 MATCH
公式中使用数组,即($您需要输入您的公式:
($ C $ 2:$ C $ 10 = A13) / p>
ctrl + shift + 输入
如果您想进一步阅读,可以看到微软的数组公式的准则和示例
I would like to lookup for a value in excel table with multiple criteria. I have tried this:
=INDEX($D$2:$D$10,MATCH(1,($B$2:$B$10=A13)*($C$2:$C$10=A13),0))
but it doesn't work.
I use this one:
=LOOKUP(2,1/(Reservation!$A:$A=$A20)/(Reservation!$B:$B=F$5)/(Reservation!$D:$D=0),Reservation!$C:$C)
... which works fine, but it's extremely slow.
Is there anyway to use index with multiple criteria?
Normally with a single criteria all you would have to do is type:
INDEX($D$2:$D$10,MATCH(A13,$B$2:$B$10,0))
And hit enter and you are done.
However, since you are using arrays inside your MATCH
formula i.e. ($B$2:$B$10=A13)
and ($C$2:$C$10=A13)
you need to enter your formula using:
ctrl + shift + enter
If you want further reading you can see Microsoft's Guidelines and examples of array formulas
这篇关于如何在excel 2007和更高版本中查找具有多个条件的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!