Looking Up the Answer by Row and Column

[ericm]

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.

 

[T. Valko]

Assume your table is in the range A15

A2:A5 = row headers
B11 = column headers
A1 is empty

A10 = lookup row header
B10 = lookup column header

=VLOOKUP(A10,A15,MATCH(B10,A11,0),0)

 

[Max]

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),"")

---

 

[ericm]

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

- Eric

 

[Lars-Åke Aspelin]

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,OFFSET(Table_range,0,0,ROWS(Table_range),1),0)-1,MATCH(Column_header,OFFSET(Table_range,0,0,1,COLUMNS(Table_range)),0)-1)

It could be used for both of your problems.

Lars-Åke

 

[ericm]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!