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
10 a Adam Andrew
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
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
 
L

laandmc

Thanks thats spot on!

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


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top