Search lowest value in a column and display the entire row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following cells:

Country Code Rate Carrier
Albania 355 .10 abc
Albania 355 .12 xbc

I need to display the entire row with the lowest "Rate." In the above
example, I need to show....Albania 355 .10 abc in a separate worksheet.
 
Additionally, I will have multiple destinations, as stated below:

Country Code Rate Carrier
Albania 355 .10 abc
Albania 355 .12 xbc
Albania - Cell 35538 .15 abc
Albania - Cell 35538 .16 xyz

Which will be sorted by the "Code." If a country has 2 carriers with the
same "Code," I need the lowest "Rate" of the 2, to display the entire row in
a separate worksheet.
 
Use MIN to get the lowest rate. And then use Index & Match to get the
other columns.

Mangesh
 
Assume the source table is in a sheet: x, cols A to D, data from row2 to
row5, where col C = rate (source table need not be sorted)

In the other sheet, supposing you have the inputs specified in A2:C2,
viz:

Country Code Carrier
Albania 35538 abc

Then in D2, array-entered**:
=MIN(IF((x!A2:A5=A2)*(x!B2:B5=B2)*(x!D2:D5=C2),x!C2:C5))
will return the required result from the source table. Adapt the ranges to
suit.

**press CTRL+SHIFT+ENTER to confirm the formula

---
 
Perhaps you might be looking for this instead ..

Assume the source table is in a sheet: x, cols A to D, data from row2 to 100
(say), where col C = rate (source table need not be sorted)

In another sheet,

Put in A2, array-enter the formula, ie press CTRL+SHIFT+ENTER to confirm the
formula:
=IF(COUNTA(x!A2:C2)<3,"",IF(MIN(IF((x!A$2:A$100=x!A2)*(x!B$2:B$100=x!B2),x!C$2:C$100))=x!C2,ROW(),""))
Leave A1 blank

Put in B2, normal ENTER:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to E2. Select A2:E2, copy down to E100. Hide away col A. Cols B to E
will return the required results, ie only the lines from the source sheet: x
with the lowest rates, all neatly bunched at the top.

---
 
Back
Top