Returning the values of a column, in increasing value, based on one additional criteria

Hi -

So, i'm trying to simplify a complicated sheet using formulas. Unfortunately, I can't use a macro.

Here is the situation. I have data in a table which has about 2,000 rows. What I need, is to have have a formula which displays values from A from small to large, filtered by column B.

so, say I have 2 columns.


Column A Column B
Row 1 1 VM
Row 2 2 VM
R3 3 GL
R4 4 GL
R5 5 VM
R6 6 VM


Desired return in column C - the COlumn A values, increasing in value, limited to only those in which there is VM in Column B

R1 1
R2 2
R3 5
R4 6


Does this make sense?

Try

in C1

=IFERROR(SMALL(IF($B$1:$B$6="VM",$A$1:$A$6),ROWS($1:1)),"")

Enter with Ctrl+Shift+Enter

Thats great! One additional thing which I forgot to mention - can this be done with multiple critiera - so where B1: B6 ="VM", AND C1:C6 = "X".

?

Try

=IFERROR(SMALL(IF(($B$1:$B$6="VM")*($C$1:$C$6="X"),$A$1:$A$6),ROWS($1:1)),"")

Shoot. It works but leaves gaps. See attached sheet.

Sorry - I didn't re-iterate ...

Enter with Ctrl+Shift+Enter

Ah! Got it! Thanks John! Works perfect.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.