Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-part LOOKUP...t'were it possible?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-part LOOKUP...t'were it possible?
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. -- Gary''s Student - gsnu200752 "Arlen" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-part LOOKUP...t'were it possible?
Say your list runs from A1 to Z2.
Try this: =SUMPRODUCT(MAX((A1:Z1=MAX(A1:Z1))*A2:Z2)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Arlen" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-part LOOKUP...t'were it possible?
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" wrote: Say your list runs from A1 to Z2. Try this: =SUMPRODUCT(MAX((A1:Z1=MAX(A1:Z1))*A2:Z2)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Arlen" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-part LOOKUP...t'were it possible?
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 ! ----------------------------------------------------------------------------------------------- "Arlen" wrote in message ... 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" wrote: Say your list runs from A1 to Z2. Try this: =SUMPRODUCT(MAX((A1:Z1=MAX(A1:Z1))*A2:Z2)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Arlen" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup part number in a vertical list and return the most recent . | Excel Discussion (Misc queries) | |||
Replace Old Part Numbers with New Part Numbers in a Macro. | Excel Discussion (Misc queries) | |||
Copying Part of a row down part of a column | Excel Discussion (Misc queries) | |||
Part Number Lookup | Excel Worksheet Functions | |||
Matrix Query Part II - lookup value | Excel Worksheet Functions |