lookup in a matrix

L

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
11 b Bob Barry

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

Cheers

L

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
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

L

laandmc

Thanks thats spot on!

Lars-Ã…ke Aspelin said:
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

.