I am trying to figure out to make a simple lookup tool that will respond
with a specific answer based on the combination of two criteria.
Specifically, I have one drop-down with 52 items on it and another
drop-down with 200 items. What I want the sheet to do is this: When a
user selects one item from the first drop-down and another item from
the second drop-down a third cell will populate with a specific answer.
Like this

First drop-down: User selects the word "RED"

Second drop-down: User selects the number "15"

Based on these selections, the sheet should find the word "LOSE" and
populate a third cell with it.

Sorry for the long-winded question. Can anyone help guide me in the
right direction? Thanks.


--
bokonon
------------------------------------------------------------------------
bokonon's Profile: http://www.excelforum.com/member.php...o&userid=31107
View this thread: http://www.excelforum.com/showthread...hreadid=507740

Hi

One way is to have a table (probably on another sheet) with your 'answers'
on. You would need a lookup column with the two terms joined. In your
example, your table would have an entry of RED15 and in the next column
would be LOSE.
Your table may look something like this:
BLACK1 WIN
BLACK2 WIN
BLACK3 LOSE
. . . . ..

Andy.


"bokonon" wrote in
message ...

I am trying to figure out to make a simple lookup tool that will respond
with a specific answer based on the combination of two criteria.
Specifically, I have one drop-down with 52 items on it and another
drop-down with 200 items. What I want the sheet to do is this: When a
user selects one item from the first drop-down and another item from
the second drop-down a third cell will populate with a specific answer.
Like this

First drop-down: User selects the word "RED"

Second drop-down: User selects the number "15"

Based on these selections, the sheet should find the word "LOSE" and
populate a third cell with it.

Sorry for the long-winded question. Can anyone help guide me in the
right direction? Thanks.


--
bokonon
------------------------------------------------------------------------
bokonon's Profile:
http://www.excelforum.com/member.php...o&userid=31107
View this thread: http://www.excelforum.com/showthread...hreadid=507740

Your table must have three columns: Color, Number and Win/Lose (or
whatever). Your example would be an entry in this table:

RED 15 LOSE

Once you have such a table you can actually proceed without even any
formulas, simply by clicking in the table and using Data | Filter |
Auto Filter. This will make your header cells similar to dropdown, in
which case you can select Color and Number and the list will be
filtered to only show this combination.

Is this good enough or do you need a formula solution?

Kostis Vezerides

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

A2 would hold Red. B2 would hold 15.



bokonon wrote:

I am trying to figure out to make a simple lookup tool that will respond
with a specific answer based on the combination of two criteria.
Specifically, I have one drop-down with 52 items on it and another
drop-down with 200 items. What I want the sheet to do is this: When a
user selects one item from the first drop-down and another item from
the second drop-down a third cell will populate with a specific answer.
Like this

First drop-down: User selects the word "RED"

Second drop-down: User selects the number "15"

Based on these selections, the sheet should find the word "LOSE" and
populate a third cell with it.

Sorry for the long-winded question. Can anyone help guide me in the
right direction? Thanks.

--
bokonon
------------------------------------------------------------------------
bokonon's Profile: http://www.excelforum.com/member.php...o&userid=31107
View this thread: http://www.excelforum.com/showthread...hreadid=507740

--

Dave Peterson