遇到大难题(关于查找区间所包含的数据),Excel高手请进!

Question

比如批号20081201(固定格式) 小号001-040(固定格式)对应商业A, 同一批号 041-080 对应商业B, 081-099对应商业C,那怎么实现比如我输入20081201 005 自动判断小号005在001-040这个区间内而生成对应的商业信息(商业A) 我试了很多次,难点就在怎么让电脑自动判断小号 001-040 中间所包含的40个小号,难道让我手动分成001 002 003一直到040吗? 这样工作量好大,请大家帮帮忙!
首先谢谢大家的回答,可能是我没把问题说清楚,这个题没有这么简单,我再说得细一点吧:
A1 为批号 20081201 B1为小号 001-040 (难点: 这个小号的一个数字就代表一件货,不是固定的,分别代表1到100件货,001-040就是代表发了批号为20081201 小号从001号开始到040号的40件货给这个商业,数量有可能是1件也可能是100件),C1为商业名字,现在要在D1输入一个批号(格式如20081201) E1输入一个小号(格式如005),要求F1显示出对应的商业名字! 因为小号不固定,所以这样就不可以用大于或小于来判断区间,只能用别的方法,大家帮帮忙!

xzk235的回答与我想要的结果很接近,是这样的:
---------A---------B-------C----------D-------------E--------------F
1 20081201 001-040 A 要找的批号 要找的小号 对应的商业名
2 20081201 041-080 B
3 20081201 081-099 C
A列是批号是不一样的,B列混乱无排序,xzk235的公式能不能做出相应变更的公式呢?

Answer 1

假设，A2输入20081201005 B2输入20081201
C2输入公式=IF((A2-B2*1000)>80,"001-040",IF((A2-B2*1000)>40,"041-080","081-099"))
D2输入公式=IF((A2-B2*1000)>80,"商品C",IF((A2-B2*1000)>40,"商品B","商品A"))

因为不知道你具体要显示什么商业信息，也不知道你的具体布局，上边这样的公式不知道是不是满足你的需要。

如果你的输入“20081201 005”不是数字格式，是字符串的话，就用右取字符串三位，再转化为数字。INT(RIGHTB(A2,3))
=IF(INT(RIGHTB(A2,3))>80,"商品C",IF(INT(RIGHTB(A2,3))>40,"商品B","商品A"))

回答者： zhang1hui - 经理 四级 9-3 14:31

-----------------------------------
问题补充：首先谢谢大家的回答,可能是我没把问题说清楚,这个题没有这么简单,我再说得细一点吧:
A1 为批号 20081201 B1为小号 001-040 (难点: 这个小号的一个数字就代表一件货,不是固定的,分别代表1到100件货,001-040就是代表发了批号为20081201 小号从001号开始到040号的40件货给这个商业,数量有可能是1件也可能是100件),C1为商业名字,现在要在D1输入一个批号(格式如20081201) E1输入一个小号(格式如005),要求F1显示出对应的商业名字! 因为小号不固定,所以这样就不可以用大于或小于来判断区间,只能用别的方法,大家帮帮忙!
-----------------------------------
也许你还没说明白，既然ABC都已经确定了，还要输入DE干什么？C列都应景说了这个是谁的，为什么还要F列？

或者我这样问，
如果A1是20081201， B1是001-040，C1是白马服装，
那D1可以不是20081201吗？E1可以是超过40的吗？F1就根据D1的货号在A列找批号，在B列找货号？D1，E1，F1虽然在第一行，但是跟A1，B1，C1可能完全没关系的，是这样吗？

F1=LOOKUP((D1&E1),(A$1:A$6&(LEFT(B$1:B$6,3))),C$1:C$6)
用了向后兼容的查找。

Answer 2

猜了下你的意思，你的表格是这种格式的？
---------A----------B-----C
1 20081201 001-040 A
2 20081201 041-080 B
3 20081201 081-099 C
现在在D1和E1输入了20081201和005后，要查找005属于001-040，返回A？
公式=LOOKUP(--E1,--(LEFT(B:B,3)),C:C)
这个公式是A列全是一样的情况，若有不同，公式改为=LOOKUP(--(D1&E1),--(A:A&(LEFT(B:B,3))),C:C)

呃，若不同的话，公式不对，A列不是升序就会出问题，若A列不为升序，公式用=LOOKUP(--E1,--IF(A:A=D1,(LEFT(B:B,3)),""),C:C)的数组公式

这些公式前提是B列的各批号为升序

问题补充：全部未排序的话用=INDEX(C1:C10,MATCH(1,(A1:A10=D1)*(--E1>=(--LEFT(0&B1:B10,4)))*(--E1<=(--RIGHT(0&B1:B10,3))),0))
数组公式CTRL+SHIFT+ENTER结束

Answer 3

用辅助列就很简单了。

插入新的c列，c1公式=a1&left(b1,3)，双击c1填充柄。

原来的cdef列变为defg列，即d列为原来的商业名列，e1输入批号，f1输入小号，g1得结果。

g1公式=vlookup(e1&f1,c:d,2,0)

Answer 4

="商业"&LOOKUP(--RIGHT(A1,3),{1,41,81;"A","B","C"})
或者:
=LOOKUP(--RIGHT(A1,3),{1,41,81;"商业A","商业B","商业C"})

Answer 5

=IF(VALUE(RIGHT(A1))<=40,"商业A",IF(VALUE(RIGHT(A1))<=80,"商业B","商业C"))