ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

VBA VLOOKUPºÃ°Ý

VBA VLOOKUPºÃ°Ý

¦U¦ì¦n¡A§Ú»Ý­n§Q¥ÎvLOOKÀ°§Ú§PÂ_¼Æ¶q¡A

1¤u§@ªí1 :.³W®æÄ椺§t¦³H¡B86¸ê®Æ¡AŪ¨ú3P-COPPPROD ¸ê®Æªí¡A25¡B28¡B29«hŪ¨ú2P-COPPPROD ¸ê®Æªí

2.¨Ã¦Û°Ê¦^¶Ç¬Û¹ïÀ³¼Æ¶q

¥i¬O¦b¼¶¼g®É­Ô¡A³£¤@ª½¸õ #[]VALUE¡AÀµ½Ð¸Ñ´b

§Ú§Q¥Î¤FVLOOKUP¥]¦í COUNTIF¡BIFO(OR()¡BSEARCH¸Õ¹L³£¨S¿ìªk¡A

¤w¸g¥d¦n´X¤Ñ¤Fqq
[attach]32712[/attach]

VLOOKUP.zip (15.29 KB)

¦^´_ 1# lilizzzz

D2=IFERROR(VLOOKUP(B2,'3P-COPPPROD'!A,4,0),VLOOKUP(B2,'2P-COPPPROD'!A,4,0))

¬O³o¼Ëªº»Ý¨D¶Ü?

TOP

¦^´_ 1# lilizzzz

=IFERROR(VLOOKUP(B2,'3P-COPPPROD'!A:D,4,0),VLOOKUP(B2,'2P-COPPPROD'!A:D,4,0))
    ³o¬O§Aªº»Ý¨D¶Ü?

TOP

¦^´_ 1# lilizzzz


Sub TEST_Vlookup()
Dim Arr, Brr, xD, i&
Set xD = CreateObject("Scripting.Dictionary")
j = 2
For sh = 2 To 1 Step -1
    With Sheets(sh)
        j = j + 1
        Arr = .Range(.[A1], .[D65536].End(xlUp))
        For i = 1 To UBound(Arr)
            xD(Arr(i, 1)) = Arr(i, j)
        Next
    End With
Next
Brr = Range([¤u§@ªí1!B2], [¤u§@ªí1!C65536].End(xlUp))
For i = 1 To UBound(Brr)
    Brr(i, 1) = xD(Brr(i, 1))
Next
[¤u§@ªí1!D2].Resize(UBound(Brr), 1) = Brr
End Sub

§ó·s D2 =IFERROR(VLOOKUP(B2,'3P-COPPPROD'!A:D,3,0),VLOOKUP(B2,'2P-COPPPROD'!A:D,4,0))

TOP

ÁÂÁ¦U¦ì¸Ñµª¡A³o´N¬O§Úªº»Ý¨D¡A«D±`·PÁ±z­Ì

TOP

¥t·Q½Ð±Ð¦U¦ì¡A¦pªG§Ú¬O¥Î§Ú¦Û¤vªº¤½¦¡­n¦p¦ó§ï¡H

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD