CODEWITHSUNDEEP
excelexcel-formula
Kevin P.
Kevin P.

Reputation: 1053

Return smallest unique value that meets criteria

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

Example Layout

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.

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...

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))

Upvotes: 1

Views: 663

Answers (2)

CallumDA
CallumDA

Reputation: 12113

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))

enter image description here

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

Upvotes: 4

ian0411
ian0411

Reputation: 4265

This array formula (click Ctrl + Shift + Enter together) should work for you.

=IFERROR(SMALL(IF($A$2:$A$31=D$1,$B$2:$B$31,""),ROW()-ROW(D$1)),"")

Upvotes: 2

Related Questions