Višekratni VLOOKUP (VLOOKUP)

Sadržaj

Imamo popis narudžbi s brojevima i nazivima robe. Htio bih, na primjer, iz stola izvući po broju narudžbe svu robu koja je u njemu uključena. Više-manje ovako:

 

Prekrasna osobina VLOOKUP (VLOOKUP) u takvoj situaciji samo će djelomično pomoći, jer može izdvojiti podatke samo po prvom pronađenom podudaranju, tj. dat će nam samo jabuke. Da biste pronašli i izdvojili sve stavke iz tablice, bolje je koristiti formulu polja. Kao ova:

=INDEKS($B$2:$B$16;NAJMANJE(IF($E $2=A2: A16;ONLINE(B2:B16)-1;»»);ONLINE()-5))

Mora se unijeti na sljedeći način:

  1. odaberite ćelije u kojima bi se rezultati trebali prikazati (u našem primjeru to je raspon D6:D20)
  2. unijeti (kopirati formulu u prvu ćeliju) raspona
  3. pritisnuti Ctrl + smjena + ući

Oduzimanje jedinice u fragmentu NIZ(B2:B16)-1 radi se zbog zaglavlja tablice. Iz istog razloga, kako bi se kompenzirao pomak u rezultirajućem rasponu u odnosu na izvorni, broj pet u fragmentu se oduzima STRING()-5

Za skrivanje #NUM! pogreške koje će se pojaviti u praznim ćelijama u rezultirajućem rasponu D6:D20, možete koristiti funkcije provjere pogrešaka IF i EOSH, zamjenjujući našu formulu s malo složenijom:

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

U programu Excel 2007 pojavila se prikladnija funkcija IFERROR - omogućuje vam kompaktnije rješavanje problema:

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

PS

U engleskoj verziji programa Excel te će funkcije izgledati ovako:

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

  • Korištenje funkcije VLOOKUP za pronalaženje podataka u tablici
  • Poboljšana verzija funkcije VLOOKUP2 koja može pretraživati ​​u bilo kojem stupcu, a ne samo u prvoj vrijednosti
  • Funkcije VLOOKUP2 i VLOOKUP3 iz PLEX dodatka
  • Što su formule polja i čemu služe?

 

Ostavi odgovor