Search/Extract Formula
 

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.

Search/Extract Formula
 

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="","",...}
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"M Moore" wrote:
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.

Search/Extract Formula
 

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

"M Moore" wrote in message
...
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.

Search/Extract Formula
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Search/Extract Formula
 

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" wrote in message
...
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="","",...}
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"M Moore" wrote:
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.

Search/Extract Formula
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
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