通过两个变量匹配数值范围对应档位数据

melaniexu

图1是数据列，主要根据变量1和2去匹配表格中的档位，从而得到绿色列的结果



数据参考图如下：


想要：
1.先匹配变量1，如果变量1为10，则匹配值锁定在变量1=10 那一行，即：<10、10、20、30、40、50、60、70、>70（其中数据和数据间代表区间）
主要是数据选择后锁定此行进行匹配有点不知道怎么弄
2.通过图1的变量2数据，找到对应的绿色数据结果。如变量1=10，变量2=15，则结果为2
3.如果，变量1对应的范围为 3~21；但变量2超过此范围，若大于21，则结果为8，小于3结果为0

简单来说，根据EXCEL中的A列和B列的数值，对照H-O表格中的数据，从而得到I2-Q2对应的值

497598075

=INDEX($I$2:$Q$2,LOOKUP(B2,OFFSET($H$2,MATCH(A2,$H$3:$H$22,0),1,,9),COLUMN($A$1:$I$1)))
这样吗？

solextrade

这么有规律，可以偷鸡了：
=MIN(8,CEILING(B2/A2,1))*(A2<B2)

毛利的乖乖熊

什么意思？- 3.如果，变量1对应的范围为 3~21；但变量2超过此范围，若大于21，则结果为8，小于3结果为0

模拟一下所有情况的结果吧

melaniexu

毛利的乖乖熊 发表于 2020-3-17 20:34
什么意思？- 3.如果，变量1对应的范围为 3~21；但变量2超过此范围，若大于21，则结果为8，小于3结果为0

...

就是如果变量1=3，变量2=14；则结果应该为5

melaniexu

solextrade 发表于 2020-3-17 20:34
这么有规律，可以偷鸡了：
=MIN(8,CEILING(B2/A2,1))*(A2

哈哈哈 不能偷鸡
我是随便列的数字，实际用的时候数据还是有点复杂

melaniexu

497598075 发表于 2020-3-17 19:24
=INDEX($I$2:$Q$2,LOOKUP(B2,OFFSET($H$2,MATCH(A2,$H$3:$H$22,0),1,,9),COLUMN($A$1:$I$1)))
这样吗？

接近真相了，但是如果变量2超过对应值，其结果应该取大一点的。
所以用起来还是不对

solextrade

=LOOKUP(,0/FREQUENCY(B2,IFERROR(0+OFFSET(I$23:P$23,-A2,),)),I$2:Q$2)

melaniexu

solextrade 发表于 2020-3-18 10:22
=LOOKUP(,0/FREQUENCY(B2,IFERROR(0+OFFSET(I$23:P$23,-A2,),)),I$2:Q$2)

我也想到了用lookup去嵌套公式，但是主要就是无法锁定我想要的那一列
lookup（变量2，对照表那一行，I2：Q2）
主要是不知道怎么去用公式把变量1对照的哪一行选定到再去做匹配

melaniexu

solextrade 发表于 2020-3-18 10:22
=LOOKUP(,0/FREQUENCY(B2,IFERROR(0+OFFSET(I$23:P$23,-A2,),)),I$2:Q$2)

老哥，我把你公式改了下，能用了
=LOOKUP(A2,0/FREQUENCY(B2,IFERROR(0+OFFSET(I$23:P$23,-A2,),)),I$2:Q$2)