Expert: Douglas M. Smith - 6/13/2004

Question
Douglas,

I saw oneof your answers on usig array formula's an dI
believe the answer to my question lies in that direction, but
I can't figure it out.

Here's what I try to achieve:

Excel has useful lookup functions, which allow for looking
up values in specified rows or columns of an array, based
on matching criteria in the irst row or column (VLOOKUP
and HLOOKUP). Basically a one dimensional look-up and
then counting down to the row number specified to select
the value in that CELL

I need a 2 dimensional look-up. For example in the
following matrix

I have listed in the first column a label (e.g. specific items
bought)
And in the first row a label (e.g. person)
For each item I have the amount of money spent on buying
the item by each person.

           John      Frank   Peter
CD-s       200      300      100
Videos   50      400      10
DVDs   250      350      500

Using the RANK function can rank the matrix on highest
expenditure i.e.

     John      Frank   Peter
CD-s      6      4      7
Videos   8      2      9
DVDs   5      3      1

The lookup function I need would help me to find the name
based on the ranking i.e.
  Name
1   Peter
2   Frank
3   Frank
4   Frank
5   John
6   John
7   Peter
8   John
9   Peter

How do a formulate the function to extract the names
based on the lookup reference (rank) in the first column of
the last table?

If you know, please tell me.

Regards,

Cees

Answer
Hi Cees,

You've got me thinking a lot harder than I'm used to on Sunday afternoon.  :-)  If MATCH worked two dimensionally, this would be an easy solution.  I've come up with a solution that involves using a second table to map column numbers to the rankings and an array formula using IF, INDEX and SUMPRODUCT to retrieve the answers:

{=INDEX($B$1:$E$1,1,SUMPRODUCT(IF($B$2:$E$4=A8,1,0),$G$2:$J$4))}

Rather than to try to explain what I did in this column, I think it would be better if I sent you a spreadsheet with everything laid out, explained and working.

Send an email to doug@abundant-solutions.com and I'll email you the file I've developed.  

Douglas M. Smith
Brainbench MVP for MS Excel
www.brainbench.com
www.abundant-solutions.com