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,

 

[xaikus506]

Hi Flashbond,

I tested this function you made, it worked like a charm! thank you so much

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.

 

[xaikus506]

I would like to add a question though, what if there are multiple modes (positions with same maximum count like for example Position A has =10, Position B has = 10 and Position C has =6), how do I can make it return Position A and B using a non-array function?

 

[jasonb75]

With the untested theory that i suggested (if it works) you could try something like

=LET(u,UNIQUE(returnRange),r,FILTER(u,u<>""),c,COUNTIFS(firstCriteriaRange,firstCriteria,secondCriteriaRange,secondCriteria,returnRange,r),FILTER(r,c=MAX(c),""))

 

[xaikus506]

With the untested theory that i suggested (if it works) you could try something like

=LET(u,UNIQUE(returnRange),r,FILTER(u,u<>""),c,COUNTIFS(firstCriteriaRange,firstCriteria,secondCriteriaRange,secondCriteria,returnRange,r),FILTER(r,c=MAX(c),""))

Hi jasonb75, My apologies for my late response I look on this formula and let you know if it works thanks!

 

[xaikus506]

Hi jasonb75, My apologies for my late response I look on this formula and let you know if it works thanks!

Hi jasonb75, I tried to use the formula you provided, It works however, I have a concern that when it cannot find a mode. It returns only the unique values from the list even though they aren't resigned yet. is there a way to tweek it like if the max is 0, return blanks? Thank you for your assistance

 

[jasonb75]

If I'm following you correctly, try

=LET(u,UNIQUE(returnRange),r,FILTER(u,u<>""),c,COUNTIFS(firstCriteriaRange,firstCriteria,secondCriteriaRange,secondCriteria,returnRange,r),FILTER(r,(c>1)*(c=MAX(c)),""))

This will ignore anything that doesn't have a count of more than 1. The part (c>1) near the end of the formula sets this threshold.

 

[xaikus506]

If I'm following you correctly, try

=LET(u,UNIQUE(returnRange),r,FILTER(u,u<>""),c,COUNTIFS(firstCriteriaRange,firstCriteria,secondCriteriaRange,secondCriteria,returnRange,r),FILTER(r,(c>1)*(c=MAX(c)),""))

This will ignore anything that doesn't have a count of more than 1. The part (c>1) near the end of the formula sets this threshold.

Hi jasonb75, Thank for this it solves my problem it worked like a charm!