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:
MicheleC
 | Femeie
25 ani
cauta Barbat
26 - 68 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #1 |
|
|
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.
_______________________________________
|
|
| |
|
| #2 |
TRaPModerator
Postari: 960
|
|
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.
|
|
| |
|
| #3 |
|
|
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
_______________________________________
|
|
| |
|
| #4 |
|
|
Source:
https://www.excelforum.com/excel-formul ... ost5240597
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.
_______________________________________
|
|
| |
|
| #5 |
TRaPModerator
Postari: 960
|
|
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:
https://www.excelforum.com/excel-formul ... rking.html
|
|
| |
|
| #6 |
|
|
corrected post #1 as it had a mistaken $ on D1
D1 was referred to mistakenly as $D$1
correct is $D1
_______________________________________
|
|
| |
|
| #7 |
TRaPModerator
Postari: 960
|
|
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)))
|
|
| |
|
| #8 |
TRaPModerator
Postari: 960
|
|
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
|
|
| |
|
| #9 |
TRaPModerator
Postari: 960
|
|
Modern, SPILL alternative, to INDEX-MATCH with 2 conditions:
Turning the formula below that gets dragged down 4 rows and right to next column (so 4 by 2 range):
| =INDEX($E$3:$AAA$3; MATCH( B$1 & $A2; INDEX($E$2:$AAA$2 & $E$1:$AAA$1; );0)) |
Into this:
| =XLOOKUP(B1:C1 & A2:A5; E2:AAA2 & E1:AAA1; E3:AAA3) |
|
|
| |
|
