Reusable VLOOKUP (VLOOKUP)

txheem

Peb muaj cov npe kev txiav txim nrog cov lej thiab npe ntawm cov khoom. Kuv xav, piv txwv li, rub tawm ntawm lub rooj los ntawm kev txiav txim naj npawb tag nrho cov khoom uas muaj nyob rau hauv nws. Ntau lossis tsawg dua li qhov no:

 

Zoo heev feature VLOOKUP (VLOOKUP) Nyob rau hauv xws li ib tug teeb meem nws yuav tsuas yog ib feem pab, vim hais tias yog muaj peev xwm rho tawm cov ntaub ntawv tsuas yog los ntawm thawj pom match, piv txwv li yuav tsuas muab rau peb. apples. Txhawm rau nrhiav thiab rho tawm txhua yam khoom los ntawm lub rooj, nws yog qhov zoo dua los siv cov qauv array. Zoo li qhov no:

=INDEX($B$2:$B$16;Tsawg(IF($E$2=A2: UA 16;LINE(B2: IB 16)-1;»»);LINE()-5))

Nws yuav tsum tau nkag mus rau hauv qab no:

  1. xaiv cov hlwb uas cov txiaj ntsig yuav tsum tau tshwm sim (hauv peb qhov piv txwv, qhov no yog qhov ntau D6: D20)
  2. nkag mus (copy cov mis rau hauv thawj cell) ntawm qhov ntau
  3. xovxwm Ctrl + ua haujlwm + Sau

Unit rho tawm hauv fragment STRING(B2:B16)-1 ua tiav vim lub rooj header. Rau tib lub laj thawj, txhawm rau them nyiaj rau kev hloov pauv hauv qhov tshwm sim ntau yam txheeb ze rau qhov qub, tus lej tsib hauv qhov tawg yog rho tawm. STRING()-5

Txhawm rau nkaum #NUM! Qhov yuam kev uas yuav tshwm sim hauv cov hlwb khoob hauv qhov tshwm sim ntau yam D6: D20, koj tuaj yeem siv qhov kev kuaj xyuas yuam kev IF thiab EOSH, hloov peb cov mis nrog ib qho nyuaj me ntsis:

=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))

Hauv Excel 2007, qhov yooj yim dua IFERROR muaj nuj nqi tshwm sim - nws tso cai rau koj los daws qhov teeb meem ntau dua:

=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

Hauv Askiv version ntawm Excel, cov haujlwm no yuav zoo li no:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)

  • Siv VLOOKUP Function los nrhiav cov ntaub ntawv hauv lub rooj
  • Ib qho kev txhim kho ntawm VLOOKUP2 muaj nuj nqi uas tuaj yeem tshawb hauv txhua kab thiab tsis yog thawj tus nqi xwb
  • VLOOKUP2 thiab VLOOKUP3 ua haujlwm los ntawm PLEX add-on
  • Cov qauv array yog dab tsi thiab lawv siv rau dab tsi?

 

Sau ntawv cia Ncua