Looking Up the Answer by Row and Column
 

I'm so new to excel that even though I know how to create some functions, I
do not know the terminology, so I don't even know what to ask to get my
answer. Bear with me.

Imagine a table: Down the leftmost column is "Apples" "Pears" "Oranges", and
across the topmost row is "Retail Price" "Quantity" "Our Cost". The rest of
the table is filled with the appropriate numbers, so when i look at it I can
easily see what "our cost" for "pears" is, or what the "quantity" of
"oranges" are.

But say I want it to be that easy for a spreadsheet user: Somewhere on the
same worksheet I have two cells, one cell to enter the name of the fruit, the
other cell to enter the property of the fruit (property may not be the right
word). But the user enters "pears" in one cell and "our cost" in the other
cell. The gears turn and out pops the appropriate response (say "$1.25") in
another cell. How do I do that?

Another table would be one that most of us remember seeing on maps: the
distance between two cities. Along the left is listed a number of cities,
and along the top is that same list of cities. To find the distance between
"New York" and "Las Vegas" you simply had to finger across one city's row and
down the other city's column until your fingers met and that was your answer.
So how do I simply do this in excel if the user types in "New York" in one
cell and "Las Vegas" in the other?

Thanks.

Looking Up the Answer by Row and Column
 

Assume your table is in the range A1:D5

A2:A5 = row headers
B1:D1 = column headers
A1 is empty

A10 = lookup row header
B10 = lookup column header

=VLOOKUP(A10,A1:D5,MATCH(B10,A1:D1,0),0)

--
Biff
Microsoft Excel MVP


"ericm" wrote in message
...
I'm so new to excel that even though I know how to create some functions,
I
do not know the terminology, so I don't even know what to ask to get my
answer. Bear with me.

Imagine a table: Down the leftmost column is "Apples" "Pears" "Oranges",
and
across the topmost row is "Retail Price" "Quantity" "Our Cost". The rest
of
the table is filled with the appropriate numbers, so when i look at it I
can
easily see what "our cost" for "pears" is, or what the "quantity" of
"oranges" are.

But say I want it to be that easy for a spreadsheet user: Somewhere on the
same worksheet I have two cells, one cell to enter the name of the fruit,
the
other cell to enter the property of the fruit (property may not be the
right
word). But the user enters "pears" in one cell and "our cost" in the
other
cell. The gears turn and out pops the appropriate response (say "$1.25")
in
another cell. How do I do that?

Another table would be one that most of us remember seeing on maps: the
distance between two cities. Along the left is listed a number of cities,
and along the top is that same list of cities. To find the distance
between
"New York" and "Las Vegas" you simply had to finger across one city's row
and
down the other city's column until your fingers met and that was your
answer.
So how do I simply do this in excel if the user types in "New York" in one
cell and "Las Vegas" in the other?

Thanks.

Looking Up the Answer by Row and Column
 

One way for your 2nd Q:
.. if the user types in "New York" in one cell and "Las Vegas" in the other?

Assume the source table is in sheet: x
where cities are listed in A2 down,
and that same list of cities is also listed in B1 across

In another sheet,
With the 2 cities input in A1:B1,
you could use:
=IF(COUNTA(A1:B1)=2,OFFSET(x!A1,MATCH(A1,x!A:A,0)-1,MATCH(B1,x!1:1,0)-1),"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Looking Up the Answer by Row and Column
 

It works beautifully. Thank you. Once I saw your formula, it made sense.

- Eric

"T. Valko" wrote:

Assume your table is in the range A1:D5

A2:A5 = row headers
B1:D1 = column headers
A1 is empty

A10 = lookup row header
B10 = lookup column header

=VLOOKUP(A10,A1:D5,MATCH(B10,A1:D1,0),0)

--
Biff
Microsoft Excel MVP


"ericm" wrote in message
...
I'm so new to excel that even though I know how to create some functions,
I
do not know the terminology, so I don't even know what to ask to get my
answer. Bear with me.

Imagine a table: Down the leftmost column is "Apples" "Pears" "Oranges",
and
across the topmost row is "Retail Price" "Quantity" "Our Cost". The rest
of
the table is filled with the appropriate numbers, so when i look at it I
can
easily see what "our cost" for "pears" is, or what the "quantity" of
"oranges" are.

But say I want it to be that easy for a spreadsheet user: Somewhere on the
same worksheet I have two cells, one cell to enter the name of the fruit,
the
other cell to enter the property of the fruit (property may not be the
right
word). But the user enters "pears" in one cell and "our cost" in the
other
cell. The gears turn and out pops the appropriate response (say "$1.25")
in
another cell. How do I do that?

Another table would be one that most of us remember seeing on maps: the
distance between two cities. Along the left is listed a number of cities,
and along the top is that same list of cities. To find the distance
between
"New York" and "Las Vegas" you simply had to finger across one city's row
and
down the other city's column until your fingers met and that was your
answer.
So how do I simply do this in excel if the user types in "New York" in one
cell and "Las Vegas" in the other?

Thanks.

Looking Up the Answer by Row and Column
 

On Thu, 8 May 2008 11:01:00 -0700, ericm
wrote:

I'm so new to excel that even though I know how to create some functions, I
do not know the terminology, so I don't even know what to ask to get my
answer. Bear with me.

Imagine a table: Down the leftmost column is "Apples" "Pears" "Oranges", and
across the topmost row is "Retail Price" "Quantity" "Our Cost". The rest of
the table is filled with the appropriate numbers, so when i look at it I can
easily see what "our cost" for "pears" is, or what the "quantity" of
"oranges" are.

But say I want it to be that easy for a spreadsheet user: Somewhere on the
same worksheet I have two cells, one cell to enter the name of the fruit, the
other cell to enter the property of the fruit (property may not be the right
word). But the user enters "pears" in one cell and "our cost" in the other
cell. The gears turn and out pops the appropriate response (say "$1.25") in
another cell. How do I do that?

Another table would be one that most of us remember seeing on maps: the
distance between two cities. Along the left is listed a number of cities,
and along the top is that same list of cities. To find the distance between
"New York" and "Las Vegas" you simply had to finger across one city's row and
down the other city's column until your fingers met and that was your answer.
So how do I simply do this in excel if the user types in "New York" in one
cell and "Las Vegas" in the other?

Thanks.

You have already got a proposal that works fine, but here is another,
more "symmetrical", one that assumes that you have the range of your
table named "The_table", the input cell for the row header named
"Row_header", and the cell for the column header named
"Column_header".

=OFFSET(INDEX(Table_range,1,1),MATCH(Row_header,OF FSET(Table_range,0,0,ROWS(Table_range),1),0)-1,MATCH(Column_header,OFFSET(Table_range,0,0,1,COL UMNS(Table_range)),0)-1)

It could be used for both of your problems.

Lars-Åke

Looking Up the Answer by Row and Column
 

Ok. Ok. Wow people, I think the last two formulas made my head hurt, but
they're beautiful nonetheless. I'm forwarding a copy of all them to my
email, cause I'm a math geek just not excel proficient, and I want to try
them all out in different situations. I'm using the first response to do
what I wanted. But as with many things once it's solved I start wondering
what else I could to to tweak it just a bit to do something else I just
thought of.

Thanks a lot everyone. Great stuff.

- Eric

"Lars-Ã…ke Aspelin" wrote:

On Thu, 8 May 2008 11:01:00 -0700, ericm
wrote:

I'm so new to excel that even though I know how to create some functions, I
do not know the terminology, so I don't even know what to ask to get my
answer. Bear with me.

Imagine a table: Down the leftmost column is "Apples" "Pears" "Oranges", and
across the topmost row is "Retail Price" "Quantity" "Our Cost". The rest of
the table is filled with the appropriate numbers, so when i look at it I can
easily see what "our cost" for "pears" is, or what the "quantity" of
"oranges" are.

But say I want it to be that easy for a spreadsheet user: Somewhere on the
same worksheet I have two cells, one cell to enter the name of the fruit, the
other cell to enter the property of the fruit (property may not be the right
word). But the user enters "pears" in one cell and "our cost" in the other
cell. The gears turn and out pops the appropriate response (say "$1.25") in
another cell. How do I do that?

Another table would be one that most of us remember seeing on maps: the
distance between two cities. Along the left is listed a number of cities,
and along the top is that same list of cities. To find the distance between
"New York" and "Las Vegas" you simply had to finger across one city's row and
down the other city's column until your fingers met and that was your answer.
So how do I simply do this in excel if the user types in "New York" in one
cell and "Las Vegas" in the other?

Thanks.

You have already got a proposal that works fine, but here is another,
more "symmetrical", one that assumes that you have the range of your
table named "The_table", the input cell for the row header named
"Row_header", and the cell for the column header named
"Column_header".

=OFFSET(INDEX(Table_range,1,1),MATCH(Row_header,OF FSET(Table_range,0,0,ROWS(Table_range),1),0)-1,MATCH(Column_header,OFFSET(Table_range,0,0,1,COL UMNS(Table_range)),0)-1)

It could be used for both of your problems.

Lars-Ã…ke

Looking Up the Answer by Row and Column
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ericm" wrote in message
...
It works beautifully. Thank you. Once I saw your formula, it made sense.

- Eric

"T. Valko" wrote:

Assume your table is in the range A1:D5

A2:A5 = row headers
B1:D1 = column headers
A1 is empty

A10 = lookup row header
B10 = lookup column header

=VLOOKUP(A10,A1:D5,MATCH(B10,A1:D1,0),0)

--
Biff
Microsoft Excel MVP


"ericm" wrote in message
...
I'm so new to excel that even though I know how to create some
functions,
I
do not know the terminology, so I don't even know what to ask to get my
answer. Bear with me.

Imagine a table: Down the leftmost column is "Apples" "Pears"
"Oranges",
and
across the topmost row is "Retail Price" "Quantity" "Our Cost". The
rest
of
the table is filled with the appropriate numbers, so when i look at it
I
can
easily see what "our cost" for "pears" is, or what the "quantity" of
"oranges" are.

But say I want it to be that easy for a spreadsheet user: Somewhere on
the
same worksheet I have two cells, one cell to enter the name of the
fruit,
the
other cell to enter the property of the fruit (property may not be the
right
word). But the user enters "pears" in one cell and "our cost" in the
other
cell. The gears turn and out pops the appropriate response (say
"$1.25")
in
another cell. How do I do that?

Another table would be one that most of us remember seeing on maps: the
distance between two cities. Along the left is listed a number of
cities,
and along the top is that same list of cities. To find the distance
between
"New York" and "Las Vegas" you simply had to finger across one city's
row
and
down the other city's column until your fingers met and that was your
answer.
So how do I simply do this in excel if the user types in "New York" in
one
cell and "Las Vegas" in the other?

Thanks.