Multiple formula parts and utilizing a table

[Marilyn]

First, I'm sorry but consider me a very basic user. I'm trying to figure out
how to do a formula.

Column A Column B
Column C
(has multiple options via dropdown) (has multiple options via dropdown) ??

A user selects an option in A and an option in B. In another sheet (same
workbook) I have a table of data on 5 columns/6 rows. Depending on their
selections in A & B, I need to have column C populated with data from a
particular cell in that table. Is this even possible?

 

[Bernard Liengme]

Hi Marilyn,
Sounds like a job for VLOOKUP
Read up on this in Help (and any books you might have)
Come back with more questions - tell us (A) what version of Excel, (b) a bit
about the data in the table, (c) example of the 'rules' like "if A = "cat",
and B = "dog" what is to be returned form the table.
best wishes

 

[Lars-Åke Aspelin]

First, I'm sorry but consider me a very basic user. I'm trying to figure out
how to do a formula.

Column A Column B
Column C
(has multiple options via dropdown) (has multiple options via dropdown) ??

A user selects an option in A and an option in B. In another sheet (same
workbook) I have a table of data on 5 columns/6 rows. Depending on their
selections in A & B, I need to have column C populated with data from a
particular cell in that table. Is this even possible?

If your "Column A" choosen value ends up in cell A1 and you "Column B"
choosen value ends up in cell B1, and the table of data in the other
sheet, which has the name Sheet2, has the A and B options as "axises"
in the table, like this

empty Bopt1 Bopt2 Bopt3 Bopt4 Bopt5
Aopt1 a b c d e
Aopt2 f g h i j
Aopt3 and so on
Aopt4
Aopt5
Aopt6

and you want the value h returned if the Column A choosen option is
Aopt2 and the Column B choosen option is Bopt3, then try the following
formula in the cell in Column C where you want the result to appear:

=INDEX(Sheet2!B2:F7,MATCH(A1,Sheet2!A2:A7,0),MATCH(B1,Sheet2!B1:F1,0))

Hope this helps / Lars-Åke

 

[Luke M]

Yes. I'm guessing A controls the row & B controls the column you want to look
at.

Example:
B C
2 x y
3 z aa

My small table has row headers of 2 and 3, column headers of B and C. The
blank spot in top-left corner is cell A1. Let's say my drop down in column A
I choose "2" and in column B dropdown I choose "C". My formula is:

=INDEX('Sheet2'!$B$2:$D$3,MATCH(A2,'Sheet2'!$A$2:$A$3,0),MATCH(B2,'Sheet2'!$B$1:$C$1,0))

The spits out the result "y".

 

[Marilyn]

I'm using Excel 2003.

Sorry, I'm not explaining this well. I'll try to give better info:

Let's say Column A asks you to pick 1 of 5 gems: Agate, Turquoise, Garnet,
Diamond, Pearl
Let's say Column B asks you to pick 1 of 6 Countries: US, Can, UK, China,
Japan, Germany

The other worksheet has a table with data (its a number in each cell)
representing all the possible permutations of those choices. However (and
this might present a problem) this row/column headers of this table are not
gems and countries. Is that a problem? Its just a stand-alone table.

So, on Column C, can a formula recognize which gem you picked in Column A,
which country you picked in Column B then go to the table and bring in the
number from a table and put it into Column C. (Eg if I picked Agate and
China, I want Column C to say 1394).

Thanks!