Co zwraca funkcja INDEKS i kiedy jej użyć?
Funkcja INDEKS zwraca wartość z komórki leżącej na przecięciu wskazanego wiersza i kolumny w podanym zakresie. Używam jej zawsze gdy muszę pobrać dane z tabeli dynamicznie, bez ręcznego sprawdzania pozycji. Sprawdza się w cennikach, raportach finansowych i wszędzie tam, gdzie numer wiersza lub kolumny zmienia się w zależności od danych wejściowych. Samodzielnie wymaga podania konkretnych numerów pozycji. Prawdziwa siła pojawia się po zagnieżdżeniu funkcji PODAJ.POZYCJĘ, która automatycznie znajduje te numery. Wtedy INDEKS staje się w pełni dynamicznym mechanizmem wyszukiwania w arkuszu kalkulacyjnym Excel.
Jak wygląda składnia INDEKS w formie tablicowej?
Forma tablicowa przyjmuje trzy argumenty: INDEKS(tablica; numer_wiersza; [numer_kolumny]). Pierwszy to zakres komórek do przeszukania, np. A1:C10. Drugi wskazuje numer wiersza wewnątrz tego zakresu. Trzeci, opcjonalny, określa numer kolumny. Gdy zakres obejmuje tylko jedną kolumnę, pomijam trzeci argument i podaję sam numer wiersza. Gdy obejmuje jedną wiersz, pomijam drugi. Jeśli numer wiersza lub kolumny wykracza poza wymiary zakresu, Excel zwróci błąd #ODSYŁ. Tę formę stosuję w 90% przypadków, bo pracuję z jednym ciągłym zakresem danych.
Jak działa forma odwołaniowa INDEKS z wieloma zakresami?
Forma odwołaniowa pozwala przeszukiwać kilka oddzielnych zakresów jedną formułą. Składnia wygląda tak: INDEKS((zakres1; zakres2; zakres3); numer_wiersza; numer_kolumny; numer_obszaru). Zakresy muszą być ujęte w dodatkowy nawias, żeby Excel nie potraktował ich jako osobnych argumentów funkcji. Czwarty argument, numer_obszaru, decyduje z którego zakresu pobrać wartość. Używam tej formy gdy mam identyczne tabele dla różnych lat lub kategorii, np. cenniki od trzech dostawców. Zamiast pisać trzy osobne formuły, łączę wszystko w jedną i przełączam obszar listą rozwijaną. Oba cenniki muszą mieć te same wymiary i układ nagłówków, inaczej wyniki będą niepoprawne.
Jak połączyć INDEKS z PODAJ.POZYCJE?
Zamiast ręcznie wpisywać numery wiersza i kolumny do funkcji INDEKS, zagnieżdżam w tych argumentach PODAJ.POZYCJĘ. Ta funkcja przeszukuje zakres i zwraca pozycję szukanej wartości jako liczbę. Cała formuła działa tak: INDEKS(zakres_wyników; PODAJ.POZYCJĘ(szukana_wartość; zakres_wyszukiwania; 0)). Zero w trzecim argumencie PODAJ.POZYCJĘ oznacza dopasowanie dokładne. Dzięki temu mogę wpisać nazwę produktu w jednej komórce, a formuła sama znajduje jego pozycję i pobiera odpowiadającą wartość z innej kolumny. Zmiana danych wejściowych natychmiast aktualizuje wynik bez ingerencji w formułę.
Wyszukiwanie w pionie – numer wiersza z PODAJ.POZYCJĘ
Najprostsze zastosowanie to pobranie jednej wartości z kolumny. PODAJ.POZYCJĘ przeszukuje pionowy zakres, np. listę nazw produktów w kolumnie A, i zwraca numer pozycji trafienia. Ten numer trafia jako drugi argument do INDEKS, który pobiera wartość z odpowiedniego wiersza w kolumnie z cenami. Zakres wyszukiwania w PODAJ.POZYCJĘ i zakres wyników w INDEKS muszą zaczynać się od tego samego wiersza. Przesunięcie o jeden wiersz to najczęstszy błąd, który zwraca poprawną wartość ale dla złego rekordu.
=INDEKS(B2:B100; PODAJ.POZYCJĘ(E1; A2:A100; 0))Wyszukiwanie dwuwymiarowe – wiersz i kolumna jednocześnie
Wyszukiwanie dwuwymiarowe wymaga dwóch funkcji PODAJ.POZYCJĘ w jednej formule INDEKS. Pierwsza znajduje numer wiersza, druga numer kolumny. Stosuję to w tabelach krzyżowych, np. cennikach z rozmiarem w wierszach i typem produktu w kolumnach. Zmiana dowolnego kryterium w komórce referencyjnej automatycznie przesuwa punkt przecięcia. W jednym arkuszu obsługuję w ten sposób kilkadziesiąt kombinacji bez kopiowania formuł.
=INDEKS(A1:D10; PODAJ.POZYCJĘ(F1; A1:A10; 0); PODAJ.POZYCJĘ(G1; A1:D1; 0))Czym INDEKS + PODAJ.POZYCJĘ przewyższa WYSZUKAJ.PIONOWO?
INDEKS z PODAJ.POZYCJĘ wyszukuje w dowolnym kierunku, podczas gdy WYSZUKAJ.PIONOWO pobiera dane wyłącznie z kolumny na prawo od kolumny wyszukiwania. To główna przewaga. Druga to wydajność: w arkuszach z tysiącami formuł wyszukiwania INDEKS + PODAJ.POZYCJĘ działa odczuwalnie szybciej, bo przeszukuje tylko potrzebny zakres zamiast całej tabeli. Trzecia przewaga to odporność na zmiany struktury. Gdy dodam nową kolumnę w środku tabeli, WYSZUKAJ.PIONOWO z numerem kolumny wpisanym na sztywno zwróci błędny wynik. INDEKS + PODAJ.POZYCJĘ odwołuje się do zakresu, nie do numeru pozycji, więc przetrwa zmianę bez korekty formuły.
Kiedy lepiej użyć X.WYSZUKAJ zamiast INDEKS?
X.WYSZUKAJ zastępuje INDEKS + PODAJ.POZYCJĘ w jednej krótszej formule i obsługuje wyszukiwanie w lewo, dokładne, przybliżone oraz z symbolami wieloznacznymi. Wybieram ją gdy pracuję wyłącznie w Microsoft 365 lub Excel 2021 i mam pewność, że plik nie trafi do użytkownika ze starszą wersją. W Excel 2019 i wcześniejszych X.WYSZUKAJ nie istnieje i formuła zwróci błąd #NAZWA?. Dlatego w plikach współdzielonych między zespołami zostaję przy INDEKS + PODAJ.POZYCJĘ. Działa w każdej wersji Excela od 2007 roku. Samo X.WYSZUKAJ nie obsługuje też formy odwołaniowej z wieloma zakresami, którą INDEKS realizuje natywnie czwartym argumentem.
Jak uniknąć błędów #ODSYŁ i #N/D w funkcji INDEKS?
Błąd #ODSYŁ pojawia się gdy numer wiersza lub kolumny przekracza wymiary zakresu podanego w pierwszym argumencie. Sprawdzam to funkcją WIERSZE lub KOLUMNY, porównując rozmiar zakresu z wartością zwracaną przez PODAJ.POZYCJĘ. Błąd #N/D oznacza, że PODAJ.POZYCJĘ nie znalazła szukanej wartości w zakresie. Najczęstszą przyczyną są niewidoczne spacje na końcu tekstu lub różnice w wielkości liter przy danych importowanych z zewnętrznych systemów. Czyszczę je funkcją USUŃ.ZBĘDNE.ODSTĘPY przed wyszukiwaniem. Oba błędy maskuję opakowując całą formułę w JEŻELI.BŁĄD, ale robię to dopiero po debugowaniu. Zbyt wczesne ukrycie błędu sprawia, że formuła zwraca pozornie poprawny wynik, a dane w raporcie są fałszywe.
Zanim dodasz JEŻELI.BŁĄD do formuły INDEKS, sprawdź ręcznie 3 losowe wyniki. W jednym projekcie maskowanie #N/D ukryło literówkę w kolumnie wyszukiwania, która przez dwa tygodnie zaniżała raport sprzedaży o 12%.
Trzy praktyczne przykłady INDEKS w codziennej pracy
Poniżej trzy scenariusze, w których INDEKS + PODAJ.POZYCJĘ rozwiązuje problem szybciej niż ręczne przeglądanie danych lub alternatywne formuły wyszukiwania.
- Dynamiczny cennik z listą rozwijaną – lista rozwijana w komórce pozwala wybrać produkt, a formuła INDEKS automatycznie pobiera cenę z tabeli cennikowej. Zmiana wyboru natychmiast aktualizuje wynik bez edycji formuły.
- Wyszukiwanie w lewo – gdy kolumna z wynikiem stoi na lewo od kolumny z kryterium wyszukiwania, WYSZUKAJ.PIONOWO nie zadziała. INDEKS + PODAJ.POZYCJĘ pobiera dane z dowolnej kolumny niezależnie od jej pozycji względem kryterium.
- Pobieranie danych z wielu arkuszy jedną formułą – forma odwołaniowa INDEKS z czwartym argumentem przełącza się między identycznymi tabelami w obrębie jednego arkusza. W połączeniu z JEŻELI lub listą rozwijaną obsługuję dane z kilku okresów bez duplikowania formuł.