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.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Cezi 21 ani
 | Femeie
21 ani
Vaslui
cauta Barbat
23 - 80 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2247
|
|
Normal formula - INDEX-MATCH returns only 1st match (if there are multiple identical matches).
=INDEX($O$2:$O$383;MATCH(H2&J2;INDEX($B$2:$B$383&$C$2:$C$383;);0))
This above is an INDEX-MATCH formula with multiple conditions in match.
An ARRAY formula can return first, 2nd etc matches (USE CTRL+SHIFT+ENTER to enter these):
First match:
=INDEX($B$2:$B$6;SMALL(IF($D1=$A$2:$A$6;ROW($A$2:$A$6)-ROW($A$2)+1);1))
Second match:
=INDEX($B$2:$B$6;SMALL(IF($D1=$A$2:$A$6;ROW($A$2:$A$6)-ROW($A$2)+1);2))
Note the 1 and 2 before the )) in the end of the above formulas. 1 means first match, 2 means second match. You can continue until n-th match.
You can add those in cells, eg A1=1; A2=2 and the formula would turn to:
=INDEX($B$2:$B$6;SMALL(IF($D1=$A$2:$A$6;ROW($A$2:$A$6)-ROW($A$2)+1);A1))
=INDEX($B$2:$B$6;SMALL(IF($D1=$A$2:$A$6;ROW($A$2:$A$6)-ROW($A$2)+1);A2))
etc.
You can even use it as an ARRAY formula with the condition in IF above ($D$1=$A$2:$A$6) not as a cell = a column (D1 = A2:A6), but a cell = an array (F2 = B1:D4):
=IFERROR(INDEX($A$1:$A$4;SMALL(IF($F2=$B$1:$D$4;ROW($B$1:$D$4)-ROW($B$1)+1);G$1));"")
=IFERROR(INDEX($A$1:$A$4;SMALL(IF($F2=$B$1:$D$4;ROW($B$1:$D$4)-ROW($B$1)+1);H$1));"")
where in G1 is the number 1, in H1 number 2 etc.
_______________________________________
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
Important note:
When linking multiple files with a complex formula (like an ARRAY formula), if file formats are .xls/.xlsx (normal excel workbook), when opening one with the other one closed it will ask you for the source file and give you an error about updating links.
You can bypass that if you make both files macro-enabled - .xlsm/.xlsb.
|
|
| pus acum 6 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 18 ani
Postari: 2247
|
|
INDEX-MATCH with multiple conditions:
2 conditions:
=INDEX(E3:E24; MATCH(K1 & K2; INDEX(C3:C24 & D3:D24;);0))
Another approach (array formula, activate with CTRL+SHIFT+ENTER)
=INDEX(E3:E24; MATCH(1; (K1=C3:C24)*(K2=D3:D24); 0))
3 conditions:
=INDEX(E3:E24; MATCH(K1 & L1 & M1; INDEX(C3:C24 & D3:D24 & B3:B24;);0))
or ARRAY formula:
=INDEX(E3:E24; MATCH(1; (K1=C3:C24)*(L1=D3:D24)*(M1=B3:B24); 0))
and so on
_______________________________________
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 18 ani
Postari: 2247
|
|
Source:
Index-Match with multiple criteria including a date interval:
=INDEX(F2:F53;MATCH(1;IF(B1=G2:G53;IF(B2+3>=E2:E53;IF(B2-3<=E2:E53;1);0);0);0))
or
=INDEX(F2:F53;MATCH(1;((G2:G53=B1)*(E2:E53<=B2+3)*(E2:E53>=B2-3));0)) |
The above is for an interval between the date input in B2-3 days and the date input in B2+3 days.
_______________________________________
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
Another 3 criteria INDEX-MATCH (example):
=INDEX($C$3:$N$12;MATCH($C18;$A$3:$A$12;0);MATCH($B$17;$C$1:$N$1;0);MATCH($B$3;$B$3:$B$12;0))
See however why for some reason it was not working on some ranges and how it was fixed:
Modificat de TRaP (acum 4 ani)
|
|
| pus acum 4 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 18 ani
Postari: 2247
|
|
corrected post #1 as it had a mistaken $ on D1
D1 was referred to mistakenly as $D$1
correct is $D1
_______________________________________
|
|
| pus acum 10 luni |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
Office 365/2024 (TOROW and TOCOL do not work in Office 2021)
=TOROW(UNIQUE(FILTER(Table3[Denumire furnizor]; Table3[Material]=D18)))
or with ranges:
=TOROW(UNIQUE(FILTER(BAZA!$X$6:$X$3627; BAZA!$E$6:$E$3627=D18)))
This is to be dragged down for a range of 'Material' codes extracted with UNIQUE. It SPILLS horizontally because of the TOROW function.
Office 2021 alternative, SPILL formula with TRANSPOSE:
=TRANSPOSE(UNIQUE(FILTER(Table3[Denumire furnizor], Table3[Material]=D18)))
or in 1 cell with separator:
=TEXTJOIN("; "; TRUE; UNIQUE(FILTER(Table3[Denumire furnizor]; Table3[Material]=D18)))
|
|
| pus acum 2 saptamani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
You can even enhance the above to display the results sorted in a desired order rather than alphabetically.
For example, I have a formula returning some units of measurement, one to five depending on material.
I want to sort them as "KG", "M3" then don't care about the remaining 3 (don't even know what they when I make the formula).
=TRANSPOSE(SORTBY(
UNIQUE(FILTER($E$7:$E$1000;IFERROR($H$7:$H$1000=J7;FALSE)));
IF(ISNUMBER(MATCH(UNIQUE(FILTER($E$7:$E$1000;IFERROR($H$7:$H$1000=J7;FALSE)));
{"KG";"M3"}; 0)); 1; 2)))
UNIQUE(FILTER($E$8:$E$589;IFERROR($H$8:$H$589=J7;FALSE))) gets the filtered list.
MATCH(...; {"KG";"M3"}; 0) checks if each value is in the priority list:
- If it's found, ISNUMBER(MATCH(...)) returns TRUE, and IF(...; 1; 2) assigns priority 1.
- If it's not found, it gets priority 2 (lower priority).
SORTBY(...; IF(...)) sorts with priority:
- "KG" and "M3" first (priority 1)
- The rest appear in any order (priority 2)
Source: ChatGPT
|
|
| pus acum 6 zile |
|