车架号自动匹配责任人

菩提之树

各位大师：
麻烦帮小弟设计一个函数公式，具体如下：
一、我们的车架号共17位数，我希望以第17位数（最后1位）和第16位数（倒数第2位）在自动匹配责任人；
二、详情：
1、车架号第17位（最后1位）数字为1,3，匹配责任人为张三；第17位为2,4，匹配李四；第17位为6,8，匹配王五；第17位为7,9，匹配赵六；              2、车架号第17位（倒数第1位）数字为0，且第16位（倒数第2位）为0,2,4,6,8，匹配李四；                                                                                        3、车架号第17位（倒数第1位）数字为0，且第16位（倒数第2位）为1,3,5,7,9，匹配张三；                                                                                        4、车架号第17位（倒数第1位）数字为5，且第16位（倒数第2位）为1,3,5,7,9，匹配赵六；                                                                                        5、车架号第17位（倒数第1位）数字为5，且第16位（倒数第2位）为0,2,4,6,8，匹配王五；
车架号匹配责任人.zip (9.66 KB, 下载次数: 46)

2019-9-17 10:40 上传

点击文件名下载附件

solextrade

IF套多了，有点晕

=IF(MOD(RIGHT(B2),5),LOOKUP(,0/FIND(RIGHT(B2),{13,24,68,79}),{"张三","李四","王五","赵六"}),IF(RIGHT(B2)>"0",IF(MOD(MID(B2,16,1),2),"赵六","王五"),IF(MOD(MID(B2,16,1),2),"张三","李四")))

2912770391

=IF(RIGHT(B4,1)*1=0,IF(MOD(MID(B4,16,1),2)=0,"李四","张三"),IF(RIGHT(B4,1)*1=5,IF(MOD(MID(B4,16,1),2)=0,"王五","赵六"),LOOKUP(RIGHT(B4,1)*1,{1,2,3,4,6,7,8,9},{"张三","李四","张三","李四","王五","赵六","王五","赵六"})))

江苏永能朱兴华

也水个：
=LOOKUP(IF(OR(--RIGHT(B2)={0,5}),MMULT(MOD(-MID(B2,{16,17},1),2),{1;10}),MOD(-RIGHT(B2),2)+(--RIGHT(B2)>5)*10),{0,1,10,11},{"李四","张三","王五","赵六"})

准提部林

尋位法:
=LOOKUP(FIND(RIGHT(B2),1379246850)*2+MOD(MID(B2,16,1),2),{2,6,10,14,19,20,21},{"张三","赵六","李四","王五","赵六","李四","张三"})

jjz5543

=IF(OR((RIGHT(B2,1))={"1","2","3","4","6","7","8","9"}),LOOKUP((RIGHT(B2,1)),{"1","2","3","4","6","7","8","9"},{"张三","李四","张三","李四","王五","赵六","王五","赵六"}),IF((RIGHT(B2,1))="0",IF(OR(MID(B2,16,1)={"0","2","4","6","8"}),"李四","张三"),IF(OR(MID(B2,16,1)={"1","3","5","7","9"}),"赵六","王五")))

jisijie630

=LOOKUP(IF(MOD(RIGHT(B2),5),LOOKUP(RIGHT(B2)/1,{0,5})+MOD(RIGHT(B2),2),RIGHT(B2)+MOD(MID(B2,16,1),2)),{0,1,5,6},{"李四","张三","王五","赵六"})

mxscgwh

学习学习！！！！！！！！！！！！！！！！

ggmmlol

=IFERROR(LOOKUP(,-FIND(RIGHT(B2),{13,24,68,79}),{"张三","李四","王五","赵六"}),CHOOSE(MOD(RIGHT(B2,2),20)/5+1,"李四","王五","张三","赵六"))

dfzaq

很实用！学习学习！