Search/Extract Formula

[M Moore]

I have 3 columns of non-consecutive data in several rows.

Column 1 has description A, description B, description C, description A,
description M, etc.

Column 2 has description AA, description AD, description AF, description AD,
description AO, etc

Column 3 has amounts.

I need a formula that searches column 1 for all instances of description A.
Based on the smallest value associated with the description A items, I want
to place the description of column 2 (description result would be in the
same row as the smallest value) into the cell the formula is written in.

In the cell next to the formula above, I want to place the value (i.e. the
smallest value) that corresponds to the first formula's answer/result.

 

[Max]

One way ..

Assuming the 3 source cols are cols A to C,
with data expected within rows 1 - 100

In D1 will be entered: description A

Place in E1, array-enter the formula by pressing CTRL+SHIFT+ENTER (instead
of just pressing ENTER):
=IF(D1="","",INDEX(B$1:B$100,MATCH(MIN(IF($A$1:$A$100=$D1,$C$1:$C$100)),$C$1:$C$100,0)))
Copy E1 to F1

E1:F1 will return the required results from cols B and C. In the event of
any ties in the minimum values for the description entered in D1, only the
"highest up" row values from cols B and C will be returned.

As-is, the array formulas in E1:F1 can be copied down to return
correspondingly for other descriptions entered in D2, D3, etc

Visually check for correct array-entry in E1 before copying across to F1.
Look for the curly braces { } which will be inserted by Excel (we don't
type-in these braces). Correctly array-entered, it'll appear in the formula
bar like this: {=IF(D1="","",...}

 

[Biff]

Hi!

Both of these formulas need to be entered as arrays using the key
combination of CTRL,SHIFT,ENTER (not just ENTER):

=INDEX(B2:B20,MATCH(MIN(IF(A2:A20="description
A",C2:C20)),IF(A2:A20="description A",C2:C20),0))

=MIN(IF(A2:A20="description A",C2:C20))

Biff

 

[Max]

Oops, sorry, errata to suggested formula in E1:

=IF(D1="","",INDEX(B$1:B$100,MATCH(MIN(IF($A$1:$A$100=$D1,$C$1:$C$100)),$C$1:$C$100,0)))

The front part of the formula should have read as:
=IF($D1="","",
(with the dollar sign to fix it to col D)

 

[Biff]

E1:F1 will return the required results from cols B and C. In the event of

any ties in the minimum values for the description entered in D1, only the
"highest up" row values from cols B and C will be returned.

...........A..............B................C
1........B..............X................2
2........A..............Y................2
3........A..............Z................2

Your formulas will return X...2 when it should return Y...2.

Biff

 

[Max]

Good catch, Biff. Thanks!

Make it in E1, array-entered as:
=IF($D1="","",INDEX(B$1:B$100,MATCH(MIN(IF($A$1:$A$100=$D1,$C$1:$C$100)),IF($A$1:$A$100=$D1,$C$1:$C$100)),0))
Then copy E1 to F1, fill down

Note that the better alternative to set it up is given in Biff's earlier
response