Search lowest value in a column and display the entire row
 

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.

Search lowest value in a column and display the entire row
 

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.




"Bud" wrote:

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.

Search lowest value in a column and display the entire row
 

Use MIN to get the lowest rate. And then use Index & Match to get the
other columns.

Mangesh





On Aug 27, 7:26 am, Bud wrote:
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.



"Bud" wrote:
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.- Hide quoted text -

- Show quoted text -

Search lowest value in a column and display the entire row
 

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!C 2:C5))
will return the required result from the source table. Adapt the ranges to
suit.

**press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bud" wrote:
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.




"Bud" wrote:

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.

Search lowest value in a column and display the entire row
 

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




"Bud" wrote:

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.