Cell = oranges when B1=x and B2=y

[Wiigit]

I don't know enough to ask this question concisely, which makes using google to find the answer difficult.

I have two pull down menus. Based on the contents of those two cells, I want something else displayed in a third cell

Please Login or Register  to view this content.

If A1 is Blue, and B1 is Oranges, C1 displays -120.

[63falcondude]

Try this:

=LOOKUP(B1,{"Apples","Grapes","Oranges"},IF(A1="Green",{-100,-110,-105},{-115,-125,-120}))

[Wiigit]

Unfortunately that answers exactly what I asked. But I missed a column because I actually need three pulldown menus...
How do I apply LOOKUP to this? (Or do I need something else?)

Please Login or Register  to view this content.

[63falcondude]

I don't understand the layout of post #3.

I recommend creating a table with 4 columns (Color, Item, Fruit, and Number) that shows all of the possible inputs and outcomes.

Once we have this, we can create a formula to look for a match in that table and return the number of that match.

[Wiigit]

My bad, that was poorly done

Please Login or Register  to view this content.

If A is Green and B is Crate and C is Grapes, D displays and answer of -305

[63falcondude]

Okay, you have a few options here.

Since there are now 12 different values, we can still hard code it like in post #2 but at this point I would suggest using this table that you have created (let's say in F2:I13) and using a simple formula like this:

=SUMIFS(I:I,F:F,A1,G:G,B1,H:H,C1)

For example, with "Blue" in A1, "Box" in B1, and "Grapes" in C1, the formula would return -310.

[Wiigit]

What I'm going to have is three pulldown menus located at E1 F1 and G1

If through pulldown menus a Green Box of Grapes is chosen, D1 will show -300

[63falcondude]

That's exactly what the solution from post #6 provides.

I tested it with A1=Green, B1=Box, and C1=Grapes and the formula in D1 resulted in -300.

This will give you the same result but it is hard coded and will be harder to understand and edit/modify:

=LOOKUP(C1,{"Apples","Grapes","Oranges"},IF(A1="Blue",IF(B1="Box",{-210,-310,-110},{-215,-305,-115}),IF(B1="Box",{-200,-300,-100},{-205,-305,-105})))

[Wiigit]

Ah, now that I've played with it I understand.
My response was based on just reading the answer rather than playing with it. I'd have tried it out, but had to step out for a bit and wanted to ensure my question was understood.

Thanks for all the help!

(I'm pretty sure there's something to mark a thread a solved, but I'm having a hard time finding it)

[63falcondude]

You're welcome. Happy to help.