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.