­ã´£³¡ªL

X©w¸q¦WºÙ§ï:
=VALUE(SUBSTITUTE(LEFT(±±ªí!A2,LEN(±±ªí!A2)-1),"3A",4))

C2¤½¦¡:
=LOOKUP("z",IF({1,0},"",VLOOKUP("B"&MATCH(-X,-{999,39,25,12})&"F/"&X,¦¨¥æ©ú²Ó!$E$2:$J$49,6,)&""))
©Î
=LOOKUP("z",IF({1,0},"",VLOOKUP("B"&5-MATCH(X,{0,13,26,40})&"F/"&X,¦¨¥æ©ú²Ó!$E$2:$J$49,6,)&""))

­ã´£³¡ªL

43A = 44
²Ä¤@­Ó4ÁÙ¬O¦P¼Ë¦³§Ò~~
¥Î3A3A ¤S¦³ÂI©Ç

¦ó¤£±N4¥þ¥Î¥t¤@­^¤å¦r»PABFÁ׶}¨ú¥N,¨Ò¦p:V
4=V
14=1V
44=VV

­ã´£³¡ªL

¦^´_ 5# olisun

"z" µ¹lookup·í¯Á¤Þ, °£¿ù¥Î, ¤£¦n¸ÑÄÀ,
"B"  >> B3F/13

¤£µM¥Î³o­Ó:
=IFERROR(VLOOKUP("B"&MATCH(-X,-{999,39,25,12})&"F/"&X,¦¨¥æ©ú²Ó!$E$2:$J$49,6,)&"","")

­ã´£³¡ªL

¦^´_ 10# olisun


6¤j = B4F/6
¦¨¥æ©ú²Ó¨S¦³~~

­ã´£³¡ªL

¦^´_ 12# olisun


"¨®¦ì¸¹½X"¦³¨âÄæ, ¨Sª`·N~~
=IFERROR(LOOKUP(1,-FIND("B"&MATCH(-X,-{999,39,25,12})&"F/"&X&"_",¦¨¥æ©ú²Ó!$E$1:$E$49&"_"&¦¨¥æ©ú²Ó!$G$1:$G$49&"_"),¦¨¥æ©ú²Ó!$J$1:$J$49),"")

­ã´£³¡ªL

©w¸q¦WºÙ:
X:=--SUBSTITUTE(LEFT(±±ªí!A2,LEN(±±ªí!A2)-1),"3A",4)
Y:'="B"&SUM(N(±±ªí!X<={0,12,25,39,999}))&"F/"&±±ªí!X

C2:
=LOOKUP("z",CHOOSE({1,2,3},"",VLOOKUP(Y,¦¨¥æ©ú²Ó!$E:$J,6,),VLOOKUP(Y,¦¨¥æ©ú²Ó!$G:$J,4,)))
©Î
=IFERROR(IFERROR(VLOOKUP(Y,¦¨¥æ©ú²Ó!$E:$J,6,),VLOOKUP(Y,¦¨¥æ©ú²Ó!$G:$J,4,)),"")

­ã´£³¡ªL

©w¸qY, §ï¦¨:
="B"&SUM(N(X<={0,12,25,39,999}))&"F/"&LEFT(A2,LEN(A2)-1)

Xl0000179-2.rar (19.53 KB)

¤U¸ü¦¸¼Æ: 11

2020-8-7 11:03

¬Ý±o²´³£ªá~~