CODEWITHSUNDEEP
arraysexcellistindexinglookup
Moritz
Moritz

Reputation: 15

Return the smallest value from a list, in which only certain values are eligeble - excel

I am having some troubles formulating my problem but I hope you understand!

I have a table of firms building production plants in foreign countries in certain years. (Columns A to C).

In a seperate table i have so-called cross-national distance measures (based on the difference in gdp of the countries). (Columns G to M). Note that the distances change per year.

A simplified version of the excel would look like this: https://new.wu.ac.at/fileadmin/wu/d/i/iib/photo/stack.JPG

What I want is a formula for the manually entered results in column D. It shall give me a result which is the following:

Let me illustrate my request with the example result i would want in cell D8:

I really hope you guys can help me, i figured out a way to find a minimum in a list and i can do it for certain years as well but the issue i am having that excel first needs to find the previously entered countries, memorize them in some kind of array and then use only these countries to consider the minimum distance.

Thank you very much!

Upvotes: 0

Views: 94

Answers (1)

barry houdini
barry houdini

Reputation: 46361

Try this "array formula" for D2 copied down

=IFERROR(SMALL(IF(COUNTIFS(A$2:A$11,A2,B$2:B$11,"<"&B2,C$2:C$11,"<>"&C2,C$2:C$11,I$1:M$1)*(G$2:G$31=B2)*(H$2:H$31=C2),I$2:M$31),1),"N/A")

confirmed with CTRL+SHIFT+ENTER

That checks three conditions for your larger table - that the header row matches a qualifying country (using COUNTIFS function based on criteria in the small table), that column G matches the current year and column H matches the current country.

If all those criteria are satisfied then the relevant values in the table are returned, and SMALL finds the smallest. If there's an error (because there are no qualifying values) then N/A is returned

In Excel 2010 or later versions you can use AGGREGATE function instead of SMALL - this is useful because it doesn't require "array entry"

=IFERROR(AGGREGATE(15,6,I$2:M$31/(COUNTIFS(A$2:A$11,A2,B$2:B$11,"<"&B2,C$2:C$11,"<>"&C2,C$2:C$11,I$1:M$1)>0)/(G$2:G$31=B2)/(H$2:H$31=C2),1),"N/A")

Upvotes: 1

Related Questions