I need help to find the 10 biggest values (of the x_value) when they are following two conditions (tag and year) that I define in two cells. I am working with these data in a table and I have configured the function LARGE in the following way:

=LARGE(IF(Table1[cont]=CONCATENATE(H$2;I$2);Table1[x_value]);ROWS(F$3;F3))

I concatenated the two conditions tag and year into a column called cont which is just applying the following: =Ai&Bi (A, column for tag and B, column for year)

The problem is that when doing this function in the different cells the output or weird numbers like 0 or others that do not correspond to the tag-year condition and most of the time, they output a #¡NUM! error.

I think it does not have to do with the data nor with the ROWS function that I use (since I have also just referenced an ordered column from 1 to 10, for doing the k, and still does not work). And I cannot think of another way to solve this problem.

Just a picture so as to get a general idea of the problem:

For instance, I am searching for AFG2014 and, since I have checked the data before, the LARGE function should output 40 which corresponds to one of the very first observations that appear in my data as illustrated in the image, but the LARGE outputs 0.

CodePudding user response：

You may use LARGE() in this way.

=LARGE(--($D$2:$D$16=$H$2&$I$2)*($E$2:$E$16),ROW(1:1))

MAX() function may work to find only maximum value.

=MAX(--($D$2:$D$16=$H$2&$I$2)*($E$2:$E$16))

You must enter the formula as array entry for non 365 version of excel. Confirm entry of formula by pressing CTRL SHIFT ENTER.