EXCEL Multiple Matches and Multiple Conditions INDEX MATCH ARRAY

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:
Profil theo23

Femeie
24 ani
Arges
cauta Barbat
26 - 45 ani

Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Multiple Matches and Multiple Conditions (INDEX, MATCH, ARRAY)

Moderat de TRaP, TonyTzu

Autor

Mesaj

Pagini: 1

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2251

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 7 ani

TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 802

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 7 ani

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2251

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: 2251

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 7 ani
Postari: 802

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 5 ani)

pus acum 5 ani

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2251

corrected post #1 as it had a mistaken $ on D1

D1 was referred to mistakenly as $D$1

correct is $D1

_______________________________________

pus acum 1 an

TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 802

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 1 luna

TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 802

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 1 luna

Pagini: 1

Mergi la