View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default a sort of GROUP BY data extraction question...

The best I could come up with yet is the following, for data in cells
A1:C20, and the required letter in E1:

=INDEX(C1:C20,MATCH(E1&LARGE(B1:B20*--(A1:A20=E1),1),A1:A20&B1:B20,0))

This will give the number in C:C for the highest value in B:B for the
given letter. Although I suspected it would not work, because it gives
a nested loop, I tried enclosing it in SUM and replacing LARGE(..., 1)
with
LARGE(..., ROW(1:2))

but it only returns the highest number and ignores the other number. If
the problem is for only two values then you can use the above formula
twice and sum the two i.e.
INDEX(..., 1 ,...) + INDEX(..., 2, ...)
but I cannot see how this could be expanded for bigger numbers.

HTH
Kostis Vezerides