2-part LOOKUP...t'were it possible?

[Guest]

Dear The Experts,

I have two rows of data.

I have used HLOOKUP to find the largest value in Row 1 and return the
corresponding value in Row 2. Easy enough (if you remember the FALSE part).

However, I now see that in some circumstances, there may be multiple cells
that tie for the largest value in Row 1. How, then do I check if there's a
tie in Row 1 and perform a second look up in Row 2 for the largest
corresponding value amongst the tying cells?


I appreciate all your time and effort.

Arlen

 

[Guest]

You can cascade either VLOOKUP or HLOOKUP. Here is an example:

Say the first lookup looks in A1 thru IV1 ( could be the max or any value) .
MATCH() tells us its at cell F1. The second lookup lokks at G1 thru IV1,
etc.

 

[RagDyer]

Say your list runs from A1 to Z2.

Try this:

=SUMPRODUCT(MAX((A1:Z1=MAX(A1:Z1))*A2:Z2))

 

[Guest]

RagDyer:

Perfect!~

I'll now try to decipher what's happening in this formula, because it's not
clear at first-through-fourth glance.

But thank you.

Arlen

 

[RagDyeR]

You're welcome, and appreciate your feed-back.

To aid in your understanding:

A convenient way to "tear down" formulas in order to analyze exactly what
they're doing is to use the <F9> function key.

Select the cell containing the formula.

*In the formula bar* - select:
A1:Z1=MAX(A1:Z1)
And hit <F9>

You'll see an array of True and False,
With True denoting each cell in the array that contains the Max value.
You could have none, one, or many.

NOW, hit <Esc> to exit this mode without destroying the formula.

Next, select:
A2:Z2
And hit <F9> again.

You'll see an array of numbers indicating the contents of A2 to Z2,
With 0's denoting any empty cells in the array.

Hit <Esc> again.

The part of the formula that multiplies these 2 arrays together:
(A1:Z1=MAX(A1:Z1))*A2:Z2
Converts the True in the first part to 1's, and the False to 0's,
Which ... when multiplied with the actual cell content numbers from the 2nd
part,
Return *only* the numbers from the 2nd part that *match* the Max numbers
from the 1st part, and 0's.

To see this, select:
(A1:Z1=MAX(A1:Z1))*A2:Z2
And hit <F9> again.

You should get the picture now.
1 X 0 = 0
0 X 0 = 0
0 X 1 = 0
0 X NUM = 0
1 X NUM = NUM

Hit <Esc> again.

So you see that in the results of this multiplication, we could have all 0's
(if cells were empty), a single number (if there was only a *single* max
number), or multiple numbers (if there were *duplicate* max numbers).

Finally, we simply wrap this array calculation in another MAX() function, to
return the highest number from all the numbers of that calc.

--

Regards,

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

RagDyer:

Perfect!~

I'll now try to decipher what's happening in this formula, because it's not
clear at first-through-fourth glance.

But thank you.

Arlen