Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Nou pe simpatie:
Profil iulian93
 | Femeie
25 ani
Ilfov
cauta Barbat
27 - 68 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #1 |
TRaPModerator
Postari: 960
|
|
Source:
https://www.get-digital-help.com/2009/0 ... -in-excel/
Array formula, just like in the example from source:
=IFERROR(INDEX($B$3:$E$12;SMALL(IF((INDEX($B$3:$E$12;;$D$16)<=$D$15)*(INDEX($B$3:$E$12;;$D$16)>=$D$14);MATCH(ROW($B$3:$E$12);ROW($B$3:$E$12));"");ROWS(B20:$B$20));COLUMNS($A$1:A1));"")
The array formula in cell B20 searches for values that meet a range criteria (cell D14 and D15), the formula lets you change the column to search in with cell D16.
This formula can be used with whatever dataset size and shape. To search the first column, type 1 in cell D16.
|
|
| |
|
| #2 |
TRaPModerator
Postari: 960
|
|
Extracting data from filtered range (array formula):
=IFERROR(INDEX(Table4[Gestiune2];SMALL(IF(((SUBTOTAL(3;OFFSET(Table4[[VALID]:[VALID]];ROW(Table4[[VALID]:[VALID]])-MIN(ROW(Table4[[VALID]:[VALID]]));;1))>0)*("VALID"=Table4[[VALID]:[VALID]]));ROW(Table4[[VALID]:[VALID]])-MIN(ROW(Table4[[VALID]:[VALID]]))+1);ROW(A1)));"")
The VALID column is a column that contains the same formula ="VALID" in every cell.
|
|
| |
|
