ML089

H2 =IF(B2<47001,0,INDEX('102¦~'!B:M,INT((B2-47001)/500)+3,K2+1))

¦V¤U½Æ»s

ML089

¦^´_ 8# aer


    ¤£¬Oµ¥¶Z®É¡A§ï¥Î¬dªí¤è¦¡³B²z
H2 =IF(B2<47001,0,LOOKUP(B2,--LEFT('102¦~'!$A$3:$A$333,FIND("~",'102¦~'!$A$3:$A$333)-1),OFFSET('102¦~'!$B$3:$B$333,,K2)))


­Y¹³9¼Ó´¡¤J¬d¸ß­È¥i¥H´£°ª¬d¸ß³t«×

ML089

¦^´_ 8# aer

«Dµ¥¶ZÁ~¸ê¥i¥H­×§ï¦p¤U
H2 =IF(B2<47001,0,INDEX('102¦~'!B:M,ROUNDUP(LOOKUP(B2,{4.7,11,12,15}*10^4+1,{3,129,139,159}-1+(B2-{4.7,11,12,15}*10^4)/{500,1000,1500,2000}),0),K2+1))



«Dµ¥¶ZÁ~¸ê¾ã²z¦p¤U
470001~110000 +0500 ROW=3
110001~120000 +1000 ROW=129
120001~150000 +1500 ROW=139
150001~500000 +2000 ROW=159 ~ 333

ML089

¦^´_ 12# aer


    À³¸Ó¬O 11

ML089

¦^´_ 13# yen956


=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,)
¦P¤U¦C2¦¡
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,0)
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,FALSE)

ML089

¦^´_ 17# yen956

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
¦pªG range_lookup ¬° TRUE ©Î³Q¬Ù²¤¡A«h¶Ç¦^§¹¥þ²Å¦X©Î¤j¬ù²Å¦Xªº­È¡C

¥¿½T¦p¤U¦¡
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2)

¤U¦¡³Ì«á¥[¤W" ," ªí¥Ü¨S¦³¬Ù²¤ range_lookup¥u¬O¨S¦³¶ñ­È¡AºD¨Ò¤WEXCELµø¬°0¡A³o¬O»{ª¾¤Wªº»~®t¡C
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,)
¦P¤U¦C2¦¡
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,0)
=VLOOKUP($B2, '102¦~'!$B$3:$N$334, $K2+2,FALSE)¡z