Lookup tables and blank cells

[grant]

I have a table consisting of columns containg data and blank cells. For
example:

A B C D E F G H
1 5700
2 5750
3 5800 83% 550 3 .77 -90 62 -16.6
4 5850
5 5900 64% 450 3 .78 -86 68 -12.5
6 5950 71% 415 3 .72 -83 58 -15.8
7 6000
8 6050 52% 304 3 .71 -77 63 -11.6
9 6100

First, I need to extract a range of maximum and minimum percentage
values in column 2 - simple enough via Large and Small (the actual
table is much larger than the illustration)

Next I need to extract their corresponding numbers from the first
column, and all subsequent corresponding values in the remaining
columns.

I've spent around five hours today trying to get beyond extracting
maximum and minimum first values via vlookup and failed.

Whether relevant, I don't know but the appearance (or non-appearance)
of the values is random, dependent on other values being present
(themselves being random).


Any help greatly appreciated. Thank you in advance.

Grant

 

[RagDyeR]

Enter this in say J1 for the highest percent:
=INDEX($A$1:$H$9,MATCH(MAX($B$1:$B$9),$B$1:$B$9,0),COLUMNS($A:A))

And enter this in J2 for the lowest percent:
=INDEX($A$1:$H$9,MATCH(MIN($B$1:$B$9),$B$1:$B$9,0),COLUMNS($A:A))

NOW, select *both* J1 and J2, and drag the selection to the right, to Column
Q.

This should give you the entire row of data, including the actual highest
and lowest percents.

This assumes that the "empty" cells in Column B are *truly empty* and
*don't* contain 0's.


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have a table consisting of columns containg data and blank cells. For
example:

A B C D E F G H
1 5700
2 5750
3 5800 83% 550 3 .77 -90 62 -16.6
4 5850
5 5900 64% 450 3 .78 -86 68 -12.5
6 5950 71% 415 3 .72 -83 58 -15.8
7 6000
8 6050 52% 304 3 .71 -77 63 -11.6
9 6100

First, I need to extract a range of maximum and minimum percentage
values in column 2 - simple enough via Large and Small (the actual
table is much larger than the illustration)

Next I need to extract their corresponding numbers from the first
column, and all subsequent corresponding values in the remaining
columns.

I've spent around five hours today trying to get beyond extracting
maximum and minimum first values via vlookup and failed.

Whether relevant, I don't know but the appearance (or non-appearance)
of the values is random, dependent on other values being present
(themselves being random).


Any help greatly appreciated. Thank you in advance.

Grant

 

[grant]

RD,

That's it. Brilliant.

You're a Gentleman (or a Lady).

Thank you.

Grant.