Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
highest # in a column A & returns answer from column B Formula | Excel Discussion (Misc queries) | |||
how do I sum a column that has #NA as an answer to a lookup? | Excel Discussion (Misc queries) | |||
How do I show complete answer column | Excel Discussion (Misc queries) | |||
column number of the answer to MIN | Excel Worksheet Functions | |||
Searching a column and returning a yes or no answer | Excel Worksheet Functions |