Reusable VLOOKUP (VLOOKUP)

Mukati

Tine rondedzero yemirairo ine nhamba uye mazita ezvigadzirwa. Ndinoda, semuenzaniso, kuburitsa kubva patafura nenhamba yekuraira zvinhu zvese zvinosanganisirwa mairi. Zvakawanda kana zvishoma seizvi:

 

Wonderful feature VLOOKUP (VLOOKUP) mumamiriro ezvinhu akadaro zvinongobatsira zvishoma, nekuti inokwanisa kuburitsa data chete neyekutanga yakawanikwa match, kureva kuti ichatipa chete. Apples. Kuti uwane uye ubvise zvinhu zvese kubva patafura, zviri nani kushandisa array formula. Sezvizvi:

=INDEX($B$2:$B$16;LEAST(IF($E$2=A2:A16;LINE(B2:B16)-1;»»);LINE()-5))

Inofanira kuiswa sezvinotevera:

  1. sarudza maseru panofanirwa kuratidzwa mhedzisiro (mumuenzaniso wedu, iyi ndiyo nhanho D6: D20)
  2. pinda (kopa fomula musero rekutanga) remhando
  3. dzvanya Ctrl + kusuduruka + pinda

Kubvisa chikamu muchidimbu STRING(B2:B16)-1 inoitwa nekuda kwemusoro wetafura. Nechikonzero chimwe chete, kutsiva kuchinjika kwechikamu chinobuda maererano nechapakutanga, nhamba shanu muchidimbu inobviswa. STRING()-5

Kuviga #NUM! kukanganisa kunozoonekwa mumaseru asina chinhu mune inokonzeresa renji D6: D20, unogona kushandisa kukanganisa kutarisa mabasa IF neEOSH, kutsiva fomula yedu neyakaoma zvishoma:

=KANA(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)))

MuExcel 2007, iri nyore IFERROR basa rakaonekwa - rinokutendera kuti ugadzirise dambudziko zvakanyanya compact:

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

PS

Mushanduro yeChirungu yeExcel, aya mabasa achaita seizvi:

=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)),»»)

  • Kushandisa VLOOKUP Basa rekutsvaga Dhata muTafura
  • Iyo yakavandudzwa vhezheni yeVLOOKUP2 basa rinogona kutsvaga mune chero column uye kwete yekutanga kukosha
  • VLOOKUP2 uye VLOOKUP3 inoshanda kubva kune PLEX yekuwedzera
  • Chii chinonzi array formula uye anoshandisirwa chii?

 

Leave a Reply