Znajdź odpowiednie dane kolumn i wierszy na podstawie danych z innej komórki w programie Excel

Ok, więc mam plik programu Excel z kilkoma tabelami, takimi jak ten na obrazku i pod linkiem poniżej.

Zrzut ekranu tabeli

https://www.dropbox.com/s/967glpkyfjfb6iy/Test.xlsx?dl=0

Usiłuję wprowadzić wartość z zakresu D2: N21 i zwrócić odpowiednią wartość komórki w kolumnach B i D oraz datę z rzędu 1 i wyświetlić je w komórkach B26 , C26 i D26 , odpowiednio.

Próbowałem używać formuł INDEX MATCH, ale wydaje mi się, że nie mogę go uruchomić. Zakładam, że czegoś mi brakuje. Każda pomoc byłaby bardzo mile widziana.

To może być wykonane bez użycia VBA. Wymaga to jednak więcej niż tylko funkcji wyszukiwania / odniesienia, ponieważ działają one tylko w jednym wymiarze (podobnie jak większość innych funkcji).

SUMPRODUCT() to jedna funkcja, która działa z tablicami dwuwymiarowymi (którą można wygenerować za pomocą prostego porównania).

Rozwiązanie wymaga tylko dwóch następujących wzorów:

Formuła 1 Wprowadzony B26i wpisany / wciśnięty z wciśniętym klawiszem Ctrl do B26:C26:

=IF(ISERROR($D26),NA(),INDEX(B:B,SUMPRODUCT(MAX(($D$2:$N$21=$A26)*(ROW($D$2:$N$21))))))

Formuła 2 Wprowadzono D26:

=INDEX($1:$1,COLUMN($D$2:$N$21)-1+MATCH($A26,INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),1):INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),COLUMNS($D$2:$N$21)),0))

Wstępnie zatwierdzona wersja formuły 2 to:

=
INDEX(
  ($1:$1),
  COLUMN($D$2:$N$21)-1
  +MATCH(
    $A26,
    INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      1
    )
    :INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      COLUMNS($D$2:$N$21)
    ),
    0
  )
)

Zauważ, że jeśli kwoty w dolarach w tabeli były unikalne, można zastosować mniej złożoną formułę. Ponieważ nie określiłeś, co chcesz zrobić, gdy zostaną znalezione duplikaty, napisałem najprostszą formułę, która nadal działa, gdy zostaną znalezione.

Tak się składa, aby wyodrębnić najbardziej pasującą do lewej wartość w dolnym wierszu zawierającym dopasowanie. Można go zmodyfikować, aby zwracał określone wartości alternatywne.

Poniższej formuły można użyć do wykrycia, czy istnieją duplikaty w tabeli dla wprowadzonej wartości A26, jeśli wymagane jest pewne powiadomienie / działanie:

=SUMPRODUCT(MAX((D2:N21=A26)*(ROW(D2:N21))))<>SUMPRODUCT(SUM((D2:N21=A26)*(ROW(D2:N21))))

W przypadku wartości unikalnych MAX()funkcja we wzorze 1 nie byłaby już oczywiście wymagana, a uproszczona formuła 2 byłaby:

=INDEX($1:$1,IFERROR(1/(1/(SUMPRODUCT(($D$2:$N$21=A26)*(COLUMN($D$2:$N$21))))),NA()))

Uwagi:

• Wstępnie sformułowana formuła faktycznie działa, jeśli zostanie wprowadzona.
• Nawiasy klamrowe ($1:$1)w gotowej wersji są wymagane do wymuszenia $1:$1pozostania na własnej linii.
• Chociaż wybrałem wyświetlanie #N/Abłędu, jeśli nie można znaleźć wprowadzonej kwoty w dolarach, można to zmienić na cokolwiek innego.

Myślę, że wszystkie funkcje wyszukiwania / referencji przeszukują kolumny lub wiersze. Chciałbym użyć „funkcji niestandardowych”, które są funkcjami VBA wchodzącymi w moduł. Potrzebujesz 3, jeden dla B26, C26 i D26. Oto jeden dla D26 (data). W D26 miałbyś „= zDate (A26)”. Moduł:

Option Explicit

Function zDate$(param$)
  Dim icol&, searchRange As Range, cellRange As Range
  Set searchRange = Range("D2:N21")
  Set cellRange = searchRange.Find(param, , xlValues, xlWhole)
  icol = cellRange.Column ' column of found cell
  zDate = Cells(1, icol) ' returns date
End Function

EDYCJA: Cóż za niesamowita odpowiedź od @robinCTS. Zamierzam kupić książkę towarzyszącą. Zostałem przeniesiony do tworzenia

=INDEX(A1:N21,1,MAX(SUMPRODUCT((D2:N21=A26)*(COLUMN(D2:N21)) )))

ale nie sprawdza duplikatów i nie rozumiem formatu „$ bbbnn.nn”. W moim przypadku A26 musiałby zawierać „$” i spacje. Dzięki