EXCEL中的双条件最近匹配查询如何构建函数？

Question

用红黄两种色灯分别组成矩形点阵，并用控制卡控制其开关顺序，以显示图文。因为卡的容量有限，卡的选择要依据色灯点阵长、宽的不同点数。每种卡能控制的不同色灯的点阵长宽点范围如下表，（黄灯中有两个灯芯，所以卡能控制的点数少,但与红灯不成比例） sheet2：
A B C D E
1 卡型号 红灯长点 红灯宽点 黄灯长点 黄灯宽点
2 AX1 36 2 20 2
3 AX1 30 3 18 3
4 AX2 58 2 40 2
5 BX 100 10 50 10
sheet1:
A B C D
1 色灯 长点 宽点 控制卡
2 黄 38 2

要求：A2,B2,C2中分别输入色灯颜色及长宽点数，需在D2中创建函数，可以在sheet2中找到最合适的卡----BX2，即色灯点阵范围（长和宽）要小于或等于卡的控制范围，并且要选择刚好够用的卡，因为容量太大的卡尽管能用，但成本高。请EXCEL高手帮助，最好留下QQ，以便以后请教，万分感谢。
有一句输入有误，应该为“在sheet1：A2,B2,C2中分别输入色灯颜色及长宽点数，需在D2中创建函数，可以在sheet2中找到最合适的卡----AX2（不是BX2）。。。”，请高手教我在D2中如何创建函数，以实现上述功能。谢谢！
对不起,忘了说明,SHEET2中的数据还有很多行,只有我没有更列出来.所以不能用列举法.

Answer 1

由于存在两个不确定因素——长、宽点数，但只有一个限制条件：求最接近的值，
因此可以列出的是“二元一次方程”且只有1个，因此得到的将是一个系列解，
所以，只有再增加一个限制条件才能得到最最符合要求的答案：比如说，在同样接近要求的数据中，以长点数最接近的为准。

在这里，长、宽点数成为一组值，如果不考虑优先参数的话，应以平均方差最小的为准。比如说，有两组数：4、8和6、9，对于目标数据5、6，我认为两者中最接近的一组是4、8。但是，如果两组数是4、8和6、8，它们对目标值5、6的接近度将是一致的。

所以，需要楼主能把以上情况的处理要求追加说明，这样答案将很快出来。追问

感谢您的关注,目标数据中无论长,还是宽,都不能超出控制卡能控制的最大点阵范围,即SHEET2中控制每种色灯的长和宽点数.因此如果目标值是5,6 最接近的应是6,8所对应的控制卡.因为4,8组合的卡,长点数小于目标值5,无法控制5*6的点阵.但可能出现两种卡的某一个控制范围(长与宽的点数)完全相同.(每种卡可以有多个控制范围,如表一的AX1).这时选择价格低的那种卡,(在SHEET2中的F列中是价格,不好意思,我忘列了)

Answer 2

这样黄、红灯混在同一列会让问题复杂化
将它们分开来吧

做了一个黄灯的
A B C D
黄色灯 长点 宽点 控制卡
16 3

D2输入数组公式

=VLOOKUP(LOOKUP(B2,{0,18.1,20.1,40.1},{18,20,40,50})&LOOKUP(C2,{0,2.1,3.1},{2,3,10}),IF({1,0},Sheet2!D$2:$D$5&Sheet2!E$2:E$5,Sheet2!A$2:A$5),2,0)

或者
=VLOOKUP(INDIRECT("sheet2!"&ADDRESS(MATCH(B2,Sheet2!D$2:D$5,1)+1,4))&INDIRECT("sheet2!"&ADDRESS(MATCH(C2,Sheet2!E$2:E$5,1)+1,5)),IF({1,0},Sheet2!D$2:$D$5&Sheet2!E$2:E$5,Sheet2!A$2:A$5),2,0)

2公式都是按CTRL+SHIFT+回车结束追问

谢谢您的回答，公式返回错误，是不是因为在SHEET2中的不同控制卡的长宽点数有时会相等，造成错误。

追答

不会吧。。。

本回答被网友采纳

Answer 3

=SUMPRODUCT(('3.1'!A2:A1000="现销")*('3.1'!D2:D1000="北山")*'3.1'!E2:E1000)

试试这个。
如果E1是文字的话就会出错

Excel帮助中#VALUE!的解释有这么一条：
更正 #VALUE! 错误
当使用的参数或操作数类型错误时，出现这种错误。当公式需要数字或逻辑值（例如 TRUE 或 FALSE）时，却输入了文本
***********
想不出来哪里出问题了。
你看看这个
A2:A1000
D2:D1000
E2:E1000
的行数是不是一致的。例如都是2:1000行
如果有一个是A1:A1000而其他是D2:D1000、E2:E1000就会出现#N/A

Answer 4

这个问题很高深呀，楼主很有才呀