Keď chcete v tabuľke v Exceli vyhľadať na základe vstupnej hodnoty inú hodnotu (napr. po zadaní názvu tovaru zistiť jeho množstvo na sklade alebo po zadaní odobraného množstva tovaru výšku zľavy), bežne sa používajú funkcie VLOOKUP a HLOOKUP. Pokročilejšou vyhľadávacou funkciou je INDEX, ktorá plnohodnotne nahradí VLOOKUP aj HLOOKUP a jej použitie je všestrannejšie.
Funkcia HLOOKUP a VLOOKUP vyžaduje, aby vyhľadávaná hodnota (kritérium) bola v prvom riadku (pre HLOOKUP) alebo v prvom stĺpci (pre VLOOKUP) tabuľky. V niektorých prípadoch je preto vhodnejšou funkcia INDEX. Vyhľadávacia funkcia INDEX takéto obmedzenie nemá a kritérium môže byť v ktoromkoľvek riadku alebo stĺpci.
V súvislosti s funkciou INDEX sa často používa funkcia MATCH, ktorá vyhľadáva relatívnu pozíciu hľadanej hodnoty. Jednoduchým príkladom sú dva stĺpce vedľa seba. V prvom je názov mesiaca (Január, Február, ...) a v druhom jeho poradové číslo (1, 2, ...). Keď skombinujeme funkcie INDEX a MATCH, tak môžeme každej relatívnej pozícii (zisťuje MATCH) mesiaca priradiť jeho názov. Hodnote 1 prislúcha Január, hodnote 2 prislúcha Február atď.
Ako tieto funkcie použiť si ukážeme na výpočte zľavy za odobrané množstvo tovaru za určité obdobie, napríklad kalendárny mesiac a to pri dvoch variantoch dohodnutých zmluvných podmienok:
- odberateľ získa zľavu už keď dosiahne dohodnutý limit,
- odberateľ získa zľavu až keď prekročí dohodnutý limit.
Funkcia VLOOKUP: Odberateľ získa zľavu, už keď dosiahne dohodnutý limit
V prvom prípade postačuje použitie funkcie VLOOKUP. Pri 1 500 kusoch odobraného tovaru funkcia =IFERROR(VLOOKUP(C9;A3:C7;3;1);0) vyhľadá v prvom stĺpci tabuľky číslo 1 500. Vyhodnotí, že najbližšie nižšie číslo je 1 000 a z tohto riadku vráti hodnotu z tretieho stĺpca, teda 1,00 %. Pri 2 000 kusoch odobraného tovaru táto funkcia vyhľadá v prvom stĺpci číslo 2 000 a z tohto riadku vráti hodnotu z tretieho stĺpca, teda 2 %. Funkcia IFERROR je v tomto prípade použitá na neuplatnenie zľavy. Ak je odobrané množstvo menšie ako 1 000 kusov (t. j. nie je splnený limit pre poskytnutie najnižšej zľavy), výsledkom vyhľadávania bude nedostupná hodnota, čo sa považuje za chybu a znamená neposkytnutie zľavy, resp. poskytnutie zľavy vo výške 0,00 %.
Funkcia INDEX: Odberateľ získa zľavu, až keď prekročí dohodnutý limit
V druhom prípade je jedným z možných riešení taký postup, že logická funkcia IF vyhodnotí, či došlo k prekročeniu minimálneho dohodnutého limitu. Ak k jeho prekročeniu nedošlo, tak funkcia INDEX vráti zľavu platnú pre predchádzajúce pásmo (napr. pri množstve 2 000 vráti zľavu 1 %, ktorá platí pri odobranom množstve > 1 000 ks a zároveň
Funkcia MATCH určuje pozíciu skutočne odobraného množstva v rámci stĺpca s minimálnymi limitmi. V našej kombinácii funkcií má dve úlohy:
- Prvou úlohou funkcie MATCH je vygenerovať hodnotu pre funkciu ISNUMBER. Práve táto funkcia poskytuje základ pre vyhodnotenie situácie, či došlo alebo nedošlo k prekročeniu dohodnutého limitu odobraného tovaru. Vo funkcii MATCH má v tomto prípade tretí argument hodnotu 0, čo znamená, že funkcia hľadá presnú zhodu. Ak odberateľ odoberie hraničné množstvo tovaru, výsledkom funkcie MATCH je číslo od 1 do 5. Funkcia ISNUMBER tento výsledok vyhodnotí ako pravdivý výrok (TRUE), lebo hodnoty 1, 2, 3, 4 a 5 sú čísla. Následne funkcia IF vyberie funkciu INDEX, ktorá sa má vykonať v prípade, ak odberateľ odoberie hraničné množstvo tovaru a ktorá vráti zľavu platnú pre predchádzajúce pásmo. V prípade, ak odberateľ odoberie akékoľvek iné množstvo okrem hraničných hodnôt jednotlivých zľavových pásiem, výsledkom funkcie MATCH nie je číslo (nie je nájdená presná zhoda) čoho dôsledkom je postup, kedy nedochádza k presunu medzi zľavovými pásmami.
- Druhou úlohou funkcie MATCH je určiť (vygenerovať číslo), v ktorom riadku má funkcia INDEX vyhľadávať príslušnú zľavu. V týchto prípadoch má tretí argument funkcie MATCH hodnotu 1, na základe čoho sa vo vzostupne usporiadanom zozname (v našom prípade minimálne limity dohodnutého odobraného množstva tovaru v ks) vyhľadáva rovnaká alebo najbližšia nižšia hodnota. Číslo -1 vo funkcii INDEX(C19:C23;MATCH(C25;A19:A23;1)-1) znamená, že sa má vyhľadať zľava v prípade, keď odberateľ odobral také množstvo tovaru, ktoré je hornou hranicou príslušného zľavového pásma, t. j. nedošlo k prekročeniu dohodnutého minimálneho limitu a chceme aby Excel vrátil zľavu pre predchádzajúce pásmo.
Opäť sa v kombinácii funkcií nachádza aj funkcia IFERROR, ktorá má rovnakú úlohu ako v prvom prípade, teda neposkytnúť zľavu, ak partner neodoberie viac ako minimálne dohodnuté množstvo (v našom prípade 1 001 kusov).
