In Excel, we have data

ABC 1 Мяч Синий 2 Мяч Красный 3 Мяч Фиолетовый 4 Мяч Зеленый 5 Мяч Круглый 6 Фигурка новогодняя 7 Фигурка наклонная 8 Ведро обычное 9 Ведро необычное 10 Ведро желтое

Interested in how to make a selection from this list. For example, I need to select all the options for the Ball value and the Bucket value at a time. Through the filter this task is impossible (only one value), = INDEX (.... MATCH ()) also gives only the first value in the list. How to bypass these restrictions?



    1 answer 1

    The first line contains the table header.

    E1, F1 - to record the selection criteria.

    Additional column with formula in D2 :

     =ЕСЛИ(ИЛИ(B2=$E$1;B2=$F$1);МАКС($D$1:D1)+1)

    Formation of the resulting table:

     =ЕСЛИ(СТРОКА(1:1)>МАКС($D$2:$D$20);"";ИНДЕКС(A$2:A$20;ПОИСКПОЗ(СТРОКА(1:1);$D$2:$D$20;)))

    It is possible to slightly reduce the formula and computations, to make MAX ($ D $ 2: $ D $ 20) in a separate cell (for example, G1 ).

    Further optimization is due to the use of the CDF () function, but for this purpose add. the column should be placed before the table (for example, in column A ; while these tables will move to the right):

     =ЕСЛИ(ИЛИ(C2=$E$1;C2=$F$1);МАКС($A$1:A1)+1) =ЕСЛИ(СТРОКА(1:1)>$G$1;"";ВПР(СТРОКА(1:1);$A$2:$D$20;СТОЛБЕЦ(B:B)))

    Without additional column to form the desired table is also possible. But it will be an array formula:

     =ИНДЕКС(A$2:A$20;НАИМЕНЬШИЙ(ЕСЛИ(($B$2:$B$20=$E$1)+($B$2:$B$20=$F$1);СТРОКА($2:$20)-1);СТРОКА(1:1)))

    Entering such a formula ends by simultaneously pressing three keys: Ctrl + Shift + Enter ; the formula should be enclosed in curly braces - {= formula} .

    Remove the error in the rows for which data was not found:

     =ЕСЛИ(СТРОКА(1:1)>СЧЁТЕСЛИ($B$2:$B$20;$E$1)+СЧЁТЕСЛИ($B$2:$B$20;$F$1);"";ИНДЕКС(...))

    The array formula produces a lot of "extra" calculations, so on large tables it is better to use the first option (with an additional column).