Excel 2007 : Drop menu problem

[Nibiru]

My apologies if I missed the threads containing the information I'm looking for.

In a nutshell, I am trying to have a cell (C1) output a certain value depending on the selections of the drop down menus in cells A1 and B1.

I have no experience with VB so I would like to avoid it if possible, but I will use it if I must.

For examples sake I would like my menus to each contain the numbers 1-4 (the easy part) and I would like C1 to output "Greater than", "Less than", or "Equal to".

[Paul]

Hi Nibiru, welcome to the forum.

Perhaps this in C1:

=IF(A1>B1,"Greater than",IF(A1<B1,"Less than","Equal to"))

[Nibiru]

Okay, now what if I were to want the numbers to be listed as words instead of actual numbers?

[Paul]

Do you mean "What if the drop downs in A1 and B1 had One, Two, Three, Four, etc., instead of 1, 2, 3, 4?"

if so, you would need to create a lookup table. In one column you would have One, Two, Three, Four, Five, Six, etc. and in the column next to it you would put 1, 2, 3, 4, 5, 6. You could then use a VLOOKUP in combination with the earlier formula, e.g.

Please Login or Register  to view this content.

This example assumes your lookup table is in columns G:H, rows 1:10.

[Nibiru]

Very interesting.. now, would this apply to other situations involving one cell being manipulated by two others?

For instance, one list containing "Salsa" and "Eggs" and the other containing "Poptarts" and "Chips". If one were to choose poptarts and salsa the word "Yuck!" would appear in C3, whereas if chips and salsa were chosen "Yum" would appear. Furthermore, if poptarts and eggs were chosen "so-so" would pop up.

Also, thank you for your warm welcome!

[Paul]

It certainly could be used in other situations. The exact solution would depend on the two other cells, of course.

[Nibiru]

How about for the situation I edited in to my last post?

[Paul]

This is quite off the path of the original question...

You could use a different lookup method, and your lookup table would need to include every combination possible plus a third column that would indicate "Yuck", "Yum" and "So-so".

Please Login or Register  to view this content.

Once that table is established, you could use an array formula like the one shown below:

=INDEX($I$1:$I$4,MATCH(A3&B3,$G$1:$G$4&$H$1:$H$4,0))

After typing or editing an array formula, you must press CTRL+SHIFT+ENTER, not just ENTER. When done properly Excel will automatically insert braces around your formula.

If you have further questions you may want to start a new thread, as your issue is not truly with drop-downs but with formulas acting on them.

[Nibiru]

Will do. Thank you for your patience and advice, I really appreciate it.