Identifying the MODE using two sets of data

Hello all,

I'm new here but have already found a ton of helpful information. Thanks! I have a set of dimensions in two columns (width (column C) and depth (column D)); here's a sample:

Data.png

I wanted to find the min, max, and mode dimensions. FYI: My results are in two cells--one cell for width and one for depth, like the input data. This code worked well for finding the minimum depth associated with the minimum width: =INDEX(Input!D18:D217,MATCH(MIN(Input!C18:C217),Input!C18:C217,0))

However, when I tried to replace "MIN" with "MODE", it returned the depth associated with the first instance of the modal width (IE 57 instead of 55).

How do I find the mode in: a subset of column D that is based on the mode of C?

If I want to return the second most-common dimension--the second-degree mode--how would I do that?

Thanks!!!

Adam

Your formula for minimum depth associated with minimum width only works co-incidentally, if it works. It will actually return the first value associated with the minimum width, as you say about the MODE version. Try this array formula for MIN

=MIN(IF(Input!C18:C217=MIN(Input!C18:C217),Input!D18:D217))

confirmed with CTRL+SHIFT+ENTER

If you want the MODE of the depth values associated with the modal width then use the same but with MODE function in place of both MIN functions