返回符合条件的最小唯一值 [英] Return smallest unique value that meets criteria

问题描述

我需要一个公式来将满足特定条件的最小唯一值返回第N个项.请看图片:

I need a formula to return the smallest unique value that meets a specific criteria, to the Nth term. Please look at image:

目标是在单元格D2:G31中有一个公式，该公式将按包含D1:G1中值的最小到最大顺序返回B2:B31的值.

Goal is to have a formula in cells D2:G31 that will return the values of B2:B31 in order of smallest to largest that contain the value in D1:G1.

例如，单元格D2将返回3，因为它是"1's"组中的最小值. D3单元格将返回12，E2单元格将返回1，等等...

For example cell D2 would return 3 because it is the smallest value in the "1's" group. Cell D3 would return 12, Cell E2 would return 1, Etc...

我尝试使用下面的代码，但是它只会返回TRUE，我不确定如何实现我的目标，但这也许会有助于您的理解.

I have tried using the code below but it just returns TRUE, I am not sure how to achieve my goal, but maybe this will help your understanding.

=AND(VLOOKUP(1,A2:B31,2),SMALL(B2:B31,1))

推荐答案

The AGGREGATE function provides a great method of solving this type of problem without needing array formulas. I mocked up a similar problem and used the following formula in cell E2 (dragging across and down)

=AGGREGATE(15,6,$B$1:$B$12/($A$1:$A$12=E$1),ROW(1:1))

请参阅吉普车的答案，以获取有关其工作原理的更好说明以及包含多个条件的示例.

See this answer from Jeeped for a better explanation of how it works and also an example with multiple criteria.

这篇关于返回符合条件的最小唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！