lookup in a matrix

[laandmc]

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

 

[Lars-Åke Aspelin]

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$11,MATCH(A2,B$10:B$11,0),MATCH(C2,C$9$9,0))

Copy down to B5.

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

Hope this helps / Lars-Åke

 

[laandmc]

Thanks thats spot on!

Try this formula in cell B2:

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

Copy down to B5.

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

Hope this helps / Lars-Ã…ke


.