lookup in a matrix
 

Hi,

I have two columns which I input data into, these correspond to the title
row in a matrix. I want to find a formula rather like Vlookup but for a
matrix which looks for the row and the column in one go. At the moment I am
getting round it by using a lengthy If function but I would really like to
get it into one formula.

See below example


A B C
1 Name Date
2 a Adam Nov <-- rows A & C are inputted, row B has the formula
3 b Bob Nov Which looks up the values from the lookup table
4 a Andrew Dec below.
5 b Barry Dec
6
7
8 Lookup Table
9 Nov Dec
10 a Adam Andrew
11 b Bob Barry


If any one could help i'd be very grateful.

Cheers

lookup in a matrix
 

On Sun, 13 Dec 2009 03:32:02 -0800, laandmc
wrote:

Hi,

I have two columns which I input data into, these correspond to the title
row in a matrix. I want to find a formula rather like Vlookup but for a
matrix which looks for the row and the column in one go. At the moment I am
getting round it by using a lengthy If function but I would really like to
get it into one formula.

See below example


A B C
1 Name Date
2 a Adam Nov <-- rows A & C are inputted, row B has the formula
3 b Bob Nov Which looks up the values from the lookup table
4 a Andrew Dec below.
5 b Barry Dec
6
7
8 Lookup Table
9 Nov Dec
10 a Adam Andrew
11 b Bob Barry


If any one could help i'd be very grateful.

Cheers

Try this formula in cell B2:

=INDEX(C$10:D$11,MATCH(A2,B$10:B$11,0),MATCH(C2,C$ 9:D$9,0))

Copy down to B5.

C$10:D$11 is the core of you Lookup Table
B$10:B$11 is the column of a, b, ... codes
C$9:D$9 is the row of Dates

Hope this helps / Lars-Åke

lookup in a matrix
 

Thanks thats spot on!

"Lars-Ã…ke Aspelin" wrote:

On Sun, 13 Dec 2009 03:32:02 -0800, laandmc
wrote:

Hi,

I have two columns which I input data into, these correspond to the title
row in a matrix. I want to find a formula rather like Vlookup but for a
matrix which looks for the row and the column in one go. At the moment I am
getting round it by using a lengthy If function but I would really like to
get it into one formula.

See below example


A B C
1 Name Date
2 a Adam Nov <-- rows A & C are inputted, row B has the formula
3 b Bob Nov Which looks up the values from the lookup table
4 a Andrew Dec below.
5 b Barry Dec
6
7
8 Lookup Table
9 Nov Dec
10 a Adam Andrew
11 b Bob Barry


If any one could help i'd be very grateful.

Cheers

Try this formula in cell B2:

=INDEX(C$10:D$11,MATCH(A2,B$10:B$11,0),MATCH(C2,C$ 9:D$9,0))

Copy down to B5.

C$10:D$11 is the core of you Lookup Table
B$10:B$11 is the column of a, b, ... codes
C$9:D$9 is the row of Dates

Hope this helps / Lars-Ã…ke


.