Lookup returning results in alphabetical order

[andrewc]

Hi,

I have data arranged as shown in columns A and B in the attached file.

I would like to create a formula in D2, which will be copied down the column, that will report the product names shown in column A, if they are listed as Grade1 in column B, in alphabetical order.

Can someone please suggest a formula that will accomplish this? (I would prefer to use a formula rather than a pivot table or filtering).

Thanks

[Fotis1991]

Hi

1 way could be using this ARRAY formula in D2 and copy down.

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[sktneer]

Another array formula with slight variation is as follows.......

Please Login or Register  to view this content.

[andrewc]

Perfect, thank you

[ragulduy]

Best I can come up with:
=IF(SMALL(IF($B$2:$B$7="Grade1",COUNTIF($A$2:$A$7,"<"&A$2:A$7),100),ROW(A1))=100,"",INDEX(A$2:A$7,MATCH(SMALL(IF($B$2:$B$7="Grade1",COUNTIF($A$2:$A$7,"<"&A$2:A$7),100),ROW(A1)),COUNTIF($A$2:$A$7,"<"&A$2:A$7),0)))
in C2 as array formula and copy down

[Fotis1991]

You are welcome and thanks for the feedback & rep.

[ragulduy]

Fotis/sktneer:

your formula's don't seem to work if I swap the order in column A
i.e. they seem to need the data to be sorted alphabetically in the first place.

[Fotis1991]

Fotis/sktneer:

your formula's don't seem to work if I swap the order in column A
i.e. they seem to need the data to be sorted alphabetically in the first place.

That's true. This is the disadvantage of our formulas! Yours has other(s).

[ragulduy]

@Fotis - it would helpful if you could provide some pointers to the disadvantages of how I did it - always looking to learn with this formula business!

[Fotis1991]

Sure my friend! Here one.

Try to type twice(more than once in any way) a name in the range. In both of these type crade1 in column B. See the results!

[ragulduy]

Ah ok, it would return duplicates values, not unique ones you mean?

[Tony Valko]

Using ROW(A1) as an incrementer leaves the formula vulnerable to new row insertions.

If you were to insert a new row 1 then ROW(A1) becomes ROW(A2) and could lead to incorrect results.

A better way is to use the ROWS function. ROWS(A$1:A1)

If a new row 1 is inserted then ROWS(A$1:A1) becomes ROWS(A$2:A2) but it still evaluates to 1 and will return the correct result.

Using the ROWS function adds some robustness without adding inefficiency or unnecessary complexity.

Also, it's better to use the cell address of the cell that contains the formula. This is mostly to reduce confusion. If the formula is entered in cell X27, some folks may see the reference to cell A$1 and wonder what that reference has to do with things.

ROWS(X$27:X27)

[Fotis1991]

Ah ok, it would return duplicates values, not unique ones you mean?

Exactly! ................

[sktneer]

If a new row 1 is inserted then ROWS(A$1:A1) becomes ROWS(A$2:A2) but it still evaluates to 1 and will return the correct result.

If I am not wrong ROWS(A$1:A1) becomes ROWS(A$1:A2).

[Tony Valko]

Try it and see what happens.

Enter this formula in cell A1:

=ROWS(A$1:A1)

The formula returns 1.

Now, insert a new row 1.

The formula is moved down to cell A2 and becomes =ROWS(A$2:A2) and still returns the correct result of 1.

[sktneer]

Oh yes, you are correct. I didn't consider the inserting a new row1, instead I thought it was being copied down.

[Tony Valko]

Yes, it is being copied down a column but I was trying to demonstrate how inserting new rows can lead to incorrect results if using ROW(A1) rather than ROWS(A$1:A1).