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:
ElizaElizaiubi Profile
 | Femeie
23 ani
Satu Mare
cauta Barbat
23 - 53 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
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: 739
|
|
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 17 ani
Postari: 2186
|
|
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 4 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
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 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
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 17 ani
Postari: 2186
|
|
corrected post #1 as it had a mistaken $ on D1
D1 was referred to mistakenly as $D$1
correct is $D1
_______________________________________
|
|
| pus acum 4 saptamani |
|