如何在VLOOKUP中有范围条件？ [英] How to have Range condition in VLOOKUP?

问题描述

我在Excle文件中有下表，我想找到相应输入的值列。

I have the below table in Excle file and i want to find the value column for the respective input.

Col A   Col B   Col C   Min  Max    Value
101      AA      High   0    10   1%
101      AA      High   11   20   2%
101      AA      Low    0    10   3%
101      AA      Low    11   20   4%
101      BB      High   0    10   5%
101      BB      High   11   20   6%
101      BB      Low    0    10   7%
101      BB      Low    11   20   8%

对于输入101，AA，Low，12 - 我应该得到4％
我已经尝试了Formula Array它的工作非常好，但是预制式并不那么好，所以我想要离开。我也不想使用DSUM或VBA。我想用简单的Excel公式实现。请帮助

For the input 101, AA, Low, 12 - i should get 4% I have already tried Formula Array and it is working great, however the prerformnace is not so good and hence i wanted to move away. Also i dont want to use DSUM or VBA. I want do achieve in plain Excel formula. Please help

推荐答案

如果没有重复的行，可以使用以下公式：

If there is no duplicate rows, you can use following formulas:

for Excel 2007或更高版本：

for Excel 2007 or later:

=SUMIFS(F2:F9,A2:A9,101,B2:B9,"AA",C2:C9,"Low",D2:D9,"<="&12,E2:E9,">="&12)

对于Excel 2003：

for Excel 2003:

=SUMPRODUCT((F2:F9)*(A2:A9=101)*(B2:B9="AA")*(C2:C9="Low")*(D2:D9<=12)*(E2:E9>=12))

其中 101 ，AA，低和 12 可以更改为单元格参考（例如 G1 ， G2 等等）

where 101, "AA", "Low" and 12 could be changed to cell references (e.g. G1,G2 and so on)

这篇关于如何在VLOOKUP中有范围条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！