Find how many people the person with the most units per alcohol per week lives with-CodePudding

I have the following data collected:

hhsize: household size

drating: totals units of alcohol per week

I am trying to find a function that tells me how many people live in the house where the individual who drinks the most units of alcohol per week lives.

I know how to find the individual that drinks the most:

=MAX(data!Q11:Q9291)

But not how to link the result to the hhsize.

CodePudding user response：

You could edit the below to match your needs:

Formula in D1:

=@SORT(A2:B9,2,-1)

Note that the 2nd parameter holds the index of the column you wish to sort by. In my case '2', in yours it's probably '14'.

CodePudding user response：

If you have Excel 365 than you can use this function:

=FILTER(data!A11:Q9291,data!Q11:Q9291 = MAX(data!Q11:Q9291))

Or to just return the two columns:

=CHOOSECOLS(FILTER(data!A11:Q9291,data!Q2:Q9291 =MAX(data!Q11:Q9291 )),1,17)

--- UPDATE ----

If you only want the first result:

=TAKE(CHOOSECOLS(FILTER(data!A11:Q9291,data!Q2:Q9291 =MAX(data!Q11:Q9291 )),1,17),1)