Index+Match+Mode Function with Multiple Criteria

[xaikus506]

Hello, I would like to seek your assistance, I would like to know how to perform/encode the formula for index+match+mode with multiple criteria.

this is what I did, however this is only good for 1 criteria only,

I also did something like this to solve my issue, but it yields inaccurate results,

 

[Legacy 143009]

I didn't get your setup exactly but usually multiple criteria search is like this:
=INDEX(yourindexcolumn,MATCH(1,((firstcriteriarange)=firstcriteria)*((secondcriteriarange)=secondcriteria),0))
This will lookup arrays so hitting Enter is not enough after paste.
Paste the formula and press Ctrl+Shift+Enter together.

 

[xaikus506]

I didn't get your setup exactly but usually multiple criteria search is like this:
=INDEX(yourindexcolumn,MATCH(1,((firstcriteriarange)=firstcriteria)*((secondcriteriarange)=secondcriteria),0))
This will lookup arrays so hitting Enter is not enough after paste.
Paste the formula and press Ctrl+Shift+Enter together.

Hi Flashbond, I am trying to generate the list of maximum occurrences (mode) of a column but with multiple conditions.

What I am trying is similar to this page: Excel formula: Most frequent text with criteria | Exceljet but with multiple criteria.

 

[Legacy 143009]

I am not familiar to use MODE function with IF.
If I were you I would modify first formula like this: ...IF(('Rawdata'!AF:AF='Dashboard'!E5)*('Rawdata'!AI:AI='Dashboard'!B10),MATCH...
Also use this with Ctrl+Shift+Enter.

 

[xaikus506]

I am not familiar to use MODE function with IF.
If I were you I would modify first formula like this: ...IF(('Rawdata'!AF:AF='Dashboard'!E5)*('Rawdata'!AI:AI='Dashboard'!B10),MATCH...
Also use this with Ctrl+Shift+Enter.

Hi Flashbond, Thank you for your advice but it still does not solve my inquiry

 

[Legacy 143009]

Hi,

Maybe someone will answer the question. But I am personally eager to try by my self. It would be great if you can provide a sample file. I want to work on it.
And please tell me which data you want to match most and what are the two criterias?

 

[xaikus506]

Hi Flashbond, Thank you for trying to help me. This is my sample file

What I am trying to do is to get the name (the answer should be either: "male", "female" or "prefer not to say") of the most occurring gender that has the most resignations last 2022 and its location is labeled as "Contractors" Please see sample file for your reference. Thanks

1. Dashboard File

Attrition Rate Report.xlsx
	A	B	C
22	Gender with Most Resignations		Female
23	Position with Most Resignations		ESG and Responsible Investment Advisory
24	Department with most Resignations		Sales
25	Division with most Resignations		Sales & Marketing
Dashboard

Cell Formulas
Range	Formula
C22	C22	=INDEX('RawData&Functions'!S:S, MODE(IF('RawData&Functions'!AH:AH=Dashboard!B10,IF('RawData&Functions'!AE:AE=Dashboard!E5, MATCH(1,('RawData&Functions'!AE:AE=Dashboard!E5)*('RawData&Functions'!AH:AH=Dashboard!B10),0),""))))
C23	C23	=INDEX('RawData&Functions'!AB:AB, MODE(IF('RawData&Functions'!AH:AH=Dashboard!B10,IF('RawData&Functions'!AE:AE=Dashboard!E5, MATCH(1,('RawData&Functions'!AE:AE=Dashboard!E5)*('RawData&Functions'!AH:AH=Dashboard!B10),0),""))))
C24	C24	=INDEX('RawData&Functions'!AD:AD, MODE(IF('RawData&Functions'!AH:AH=Dashboard!B10,IF('RawData&Functions'!AE:AE=Dashboard!E5, MATCH(1,('RawData&Functions'!AE:AE=Dashboard!E5)*('RawData&Functions'!AH:AH=Dashboard!B10),0),""))))
C25	C25	=INDEX('RawData&Functions'!AC:AC, MODE(IF('RawData&Functions'!AH:AH=Dashboard!B10,IF('RawData&Functions'!AE:AE=Dashboard!E5, MATCH(1,('RawData&Functions'!AE:AE=Dashboard!E5)*('RawData&Functions'!AH:AH=Dashboard!B10),0),""))))
Press CTRL+SHIFT+ENTER to enter array formulas.

And this is my Raw Data

Attrition Rate Report.xlsx
	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
4	Trimmed Gender	Start Date Mod	Leaving Date Mod	Job Start Date Mod	Job End Date Mod	Resigned (Y/N)	Number of Years Rendered (if Resigned)	Number of Years Rendered (Not Resigned)	Total Number of Years Rendered	Job Title (Trimmed)	Division (Trimmed)	Department Trimmed	Location	Start Date (YYYY)	Start Date (MMMM)
5	Male	May 20, 2011		January 01, 2020		No		11.45	11.00	Senior ESG Research Analyst	Research	ESG Research	Contractors	2011	May
6	Male	June 01, 2006		July 01, 2022		No		16.41	16.00	Chief Executive Officer	Executive Committee	Corporate Services	Switzerland	2006	June
7	Male	May 23, 2022		May 23, 2022		No		0.44	0.00	Sales Operations Analyst	Sales & Marketing	Sales Operations	Philippines	2022	May
8	Female	April 09, 2012		##############		No		10.56	10.00	ESG Research Analyst	Research	ESG Research	Contractors	2012	April
9	Male	March 01, 2022		March 01, 2022		No		0.67	0.00	DevOps Engineer	Products	Information Technology	Canada	2022	March
10	Male	June 16, 2021		July 05, 2022		No		1.37	1.00	ESG Advisor	Sales & Marketing	Sales	Philippines	2021	June
RawData&Functions

Cell Formulas
Range	Formula
S5:S10	S5	=TRIM(E5)
T5:T10	T5	=DATEVALUE(TRIM(TEXT(F5,"MMMM DD, YYYY")))
U5:U10	U5	=IFERROR(DATEVALUE(TRIM(TEXT(G5,"MMMM DD, YYYY"))),"")
V5:V10	V5	=DATEVALUE(TRIM(TEXT(K5,"MMMM DD, YYYY")))
W5:W10	W5	=IFERROR(DATEVALUE(TRIM(TEXT(L5,"MMMM DD, YYYY"))),"")
X5:X10	X5	=IF(AND(T5<>"",U5<>""),"Yes","No")
Y5:Y10	Y5	=IF(X5="No","",NUMBERVALUE(CONVERT(U5-T5,"day","yr")))
Z5:Z10	Z5	=IF(X5="No",NUMBERVALUE(CONVERT(TODAY()-T5,"day","yr")),"")
AA5:AA10	AA5	=IF(Z5<>"",TRUNC(NUMBERVALUE(Z5)),TRUNC(NUMBERVALUE(Y5)))
AB5:AB10	AB5	=TRIM(J5)
AC5:AC10	AC5	=IF(ISNUMBER(SEARCH({"Not in Use"},M5)), TRIM(TEXTBEFORE(TEXTAFTER(M5,"(",-1,0),")",1,0)),TRIM(M5))
AD5:AD10	AD5	=IF(ISNUMBER(SEARCH("Not in Use",N5)), TRIM(TEXTBEFORE(TEXTAFTER(N5,"(",-1,0),")",1,0)), IF(N5="N/A",TRIM(AC5),TRIM(N5)))
AE5:AE10	AE5	=TRIM(TEXTAFTER(O5,",",-1,0,1))
AF5:AF10	AF5	=NUMBERVALUE(TEXT(T5,"YYYY"))
AG5:AG10	AG5	=TEXT(T5,"MMMM")

 

[Legacy 143009]

I think you have a country name in Dashboard!E5. Thats OK. What kind of information do you have in Dashboard!B10 and Rawdata!AH?

 

[Legacy 143009]

I think my second suggestion in post#4 was correct. This works for me:

=INDEX(returnRange,MODE(IF((firstCriteriaRange=firstCriteria)*(secondCriteriaRange=secondCriteria),MATCH(returnRange,retunRange,0))))

Of course, this is an array function.

 

[jasonb75]

Untested theory

=LET(r,UNIQUE(returnRange),c,COUNTIFS(firstCriteriaRange,firstCriteria,secondCriteriaRange,secondCriteria,returnRange,r),LOOKUP(MAX(c),c,r))