return a reference from an x and y axis

  • Thread starter Thread starter jon
  • Start date Start date
J

jon

I have a table that uses horizontal and vertical headings.
I would like to return a value in the table based on the
values from both the horizontal and vertical heading. Is
there any way to do this. I know that you can use V or
Hlookups for single axis arrays, though this will not work
for my situation.

ie. |s1|s2|s3 I would like to return the answer 35
Car1|10|20|30 based on the coordinate car2 and s3
Car2|15|25|35
 
You can use a combination of index and match or offset and match

=INDEX($A$1:$D$3,MATCH("car2",$A$1:$A$3,0),MATCH("s3",$A$1:$D$1,0))

or

=OFFSET($A$1,MATCH("car2",$A$1:$A$3,0)-1,MATCH("s3",$A$1:$D$1,0)-1)
 
Jon

One method is to select the entire table A1:C3 then choose Insert>Name>Create,
and select top row and left column.

Then use the intersect functionality:

=car2 ss3

This will return the value of the cell at the intersection of the row car2 and
column ss3 which is 35.

Note: you would have to alter the s1, s2, s3 to something Excel doesn't think
is a cell address or it will return #NULL!

I used ss1, ss2, ss3

Gord Dibben Excel MVP
 
Jon

Actually Excel will append an underscore to the s1, s2, s3 when creating the
names as in s3_

Without altering the s3 on your sheet use =car2 s3_

Gord
 
Back
Top