Index/Match/Min

  • Thread starter Thread starter aresar
  • Start date Start date
A

aresar

If I am looking for a value in a list

Column A is what I am referencing, and Column B is the data I want t
use

The list is not in order. There may be duplicate references to th
number I am looking up.

If there are duplicates, I want to extract the one which has the valu
being the least in Column B
 
I looked at that site and there is no example that follows this funtio
and what I am trying to do. Please Help
 
Hi
try the following:
- sort your range with column B (ascending)
- now enter in C1 the following formula
=IF(COUNTIF($A$1:A1,A1)>1,"Duplicate to delete","")
- copy this formula down for all rows
- no apply a filter for this range and filter all entries in column C
- delete the filtered rows
 
Sample

A B

1 525.00
2 526.25
3 538.86
4 540.98

2 525.00
5 527.65
6 538.65

3 540.38
7 542.98
8 555.55

Example:
If I am looking for the value for 2, the answer is 525.00.
If I am looking for the value for 3, the answer is 538.86.

Note: The numbers cannot be re-ordered to be ascending or descending.
The grouping has to stay the same.
 
Hi
o.k. try the following formula in C1
=IF(AND(COUNTIF(A1,$A$1:$A$1000)>1,B1<>MIN(IF($A$1:$A$1000,$B$1:$B$1000
))),"Delete this","")

you have to enter this as an array formula (with CTRL+SHIFT+ENTER).
copy this formula down and do the filtering/deleting
note: If there are two values in column B which are identical to the
minimum this won't work
 
I placed in the formula, but all of the cells were blank. Any other
ideas would be appreciated.
 
Yes....did the 3 key combo. Did you test it and it worked? It seems a
if the way it is set up that it either return the "delete this" or
"blank cell". Am I looking at the function right
 
Hi
if you get these two different result than the formula is working
correctly. After applying the formula you still have to filter the data
(for all 'delet this' rows') and manually delete these rows
 
Let A1:B13 house the sample you provided, including labels:

{"A","B";1,525;2,526.25;3,538.86;4,540.98;0,0;2,525;5,527.65;6,538.65;"","";
3,540.38;7,542.98;8,555.55}

where A and B are labels and "" stands for empty cells.

In D1 enter: A (the database field with A1:B13 taken as database).

In D2 enter: 2 (criterion/condition that must hold for the first column of
the database)

In D3 enter:

=DMIN($A$1:$B$13,2,$D$1:$D$2)

The same computation can be secured using an control+shift+enter'ed formula:

=MIN(IF((A2:A13=F1)*(B2:B13),B2:B13,""))

where F1 houses a condition like 2 that must hold for A2:A13.
 
Back
Top