I-VLOOKUP Esebenziseka Kabusha (VLOOKUP)

Okuqukethwe

Sinohlu lwama-oda anezinombolo namagama ezimpahla. Ngingathanda, isibonelo, ukukhipha etafuleni ngenombolo ye-oda zonke izimpahla ezifakwe kuyo. Okuningi noma okuncane okufana nalokhu:

 

Isici esimangalisayo I-VLOOKUP (VLOOKUP) esimweni esinjalo kuzosiza kancane, ngoba ikwazi ukukhipha idatha kuphela ngokufanisa kokuqala okutholiwe, okungukuthi izosinika kuphela Apples. Ukuze uthole futhi ukhiphe zonke izinto etafuleni, kungcono ukusebenzisa ifomula yohlu. Njengalena:

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

Kumele ifakwe kanje:

  1. khetha amaseli lapho imiphumela kufanele iboniswe khona (esibonelweni sethu, lolu wububanzi D6:D20)
  2. faka (kopisha ifomula kuseli lokuqala) lobubanzi
  3. Cindezela Ctrl + Shift + Faka

Ukukhipha iyunithi kucezu STRING(B2:B16)-1 kwenziwa ngenxa yesihloko setafula. Ngesizathu esifanayo, ukuze kunxeshezelwe ukushintsha kobubanzi obuwumphumela ngokuhlobene nowokuqala, inombolo yesihlanu esiqeshini iyasuswa. STRING()-5

Ukufihla i-#NUM! iphutha elizovela kumaseli angenalutho ebangeni eliwumphumela we-D6:D20, ungasebenzisa iphutha ukuhlola imisebenzi ethi IF kanye ne-EOSH, esikhundleni sefomula yethu ngeyinkimbinkimbi kakhudlwana:

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

Ku-Excel 2007, kwavela umsebenzi we-IFERROR olula kakhulu - ikuvumela ukuthi uxazulule inkinga ngobumbene:

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

PS

Enguqulweni yesiNgisi ye-Excel, le misebenzi izobukeka kanje:

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

  • Ukusebenzisa Umsebenzi we-VLOOKUP ukuthola Idatha Kuthebula
  • Inguqulo ethuthukisiwe yomsebenzi we-VLOOKUP2 engasesha kunoma iyiphi ikholomu hhayi nje inani lokuqala
  • I-VLOOKUP2 kanye nemisebenzi ye-VLOOKUP3 ivela kusengezo se-PLEX
  • Ayini amafomula afanayo futhi asetshenziselwa ini?

 

shiya impendulo