2 dimension lookup

[Guest]

Hi Everybody,


if this statement --> " =VLOOKUP(A2,Sheet1!$A$2:$B$3,2,0) " is lookup for a
table by 1 criteria (1-Dimension).

How about looking up a 2-Dimension, let's illustrate.

in Sheet1:
Class1 Peter
Class2 Mary
Class3 Ben

=VLOOKUP(A2,Sheet1!$A$1:$B$3,2,0) '1-Dimension search, if Class1, then
show Peter, right.


How about in 2-Dimension:

Eng Maths Chem
Class1 Peter John Ann
Class2 Mary Jeff Ken
Class3 Ben Peter Mary

Kindly tells what's the lookup statement to set criteria Class3 and Maths,
then Peter found.

Thanks & regards!

 

[JulieD]

Hi

=INDEX(A14,MATCH("Class3",A1:A4,0),MATCH("Maths",A11,0))

OR
=INDEX(A14,MATCH(G1,A1:A4,0),MATCH(H1,A11,0))
where G1 has Class3 and
H1 has "Maths"

Regards
JulieD

 

[Tom Ogilvy]

=index(A14,Match(A4,A1:A4,0),Match(C1,A11,0))

 

[Alan Beban]

Hi Everybody,


if this statement --> " =VLOOKUP(A2,Sheet1!$A$2:$B$3,2,0) " is lookup for a
table by 1 criteria (1-Dimension).

How about looking up a 2-Dimension, let's illustrate.

in Sheet1:
Class1 Peter
Class2 Mary
Class3 Ben

=VLOOKUP(A2,Sheet1!$A$1:$B$3,2,0) '1-Dimension search, if Class1, then
show Peter, right.


How about in 2-Dimension:

Eng Maths Chem
Class1 Peter John Ann
Class2 Mary Jeff Ken
Class3 Ben Peter Mary

Kindly tells what's the lookup statement to set criteria Class3 and Maths,
then Peter found.

Thanks & regards!

A simple intersection operator is provided for a table like this,
involving naming the rows and columns according to your setup. Highlight
your data, including row and column headings; click Insert|Name|Create,
check Top row and Left Column and OK. Then =Maths Class3 or
=Class3 Maths will return Peter. That is, the space is the Intersection
Operator to find the value of the cell at the intersection of the headers.

Alan Beban

Alan Beban

 

[Guest]

All the methods work fine, thanks for your huge help.