Easiest way for 300+ outcomes from two conditions

[Jbone697]

I have two drop-down list boxes with multiple options to choose from in each box. Doing the math, there will end up being 300+ outcomes in the destination cell. I thought about doing IF AND statements and I'm pretty sure excel won't allow for that to happen. I have tables with all of the possible outcomes, and I'm wondering if there's a way I can make this happen.


To elaborate, this is for work. We have multiple (14) shifts, with multiple (22) starting lunch times. I want to select a shift and a start lunch time so in the destination cell, it displays the appropriate starting shift time.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

ShiftCol​

[/td][td="bgcolor:#F3F3F3"]

LunchCol​

[/td][td="bgcolor:#F3F3F3"]

StartCol​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Day[/td][td]

11:00​

[/td][td]

7:00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Day[/td][td]

12:00​

[/td][td]

8:00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td="bgcolor:#F3F3F3"]

ThisShift​

[/td][td="bgcolor:#F3F3F3"]

ThisLunch​

[/td][td="bgcolor:#F3F3F3"]

ThisStart​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Day[/td][td]

12:00​

[/td][td="bgcolor:#CCFFCC"]

8:00​

[/td][td="bgcolor:#CCFFCC"]C9: {=INDEX(StartCol, MATCH(1, (ShiftCol=ThisShift) * (LunchCol=ThisLunch), 0))}[/td][/tr]
[/table]

 

[Jbone697]

Thank you for your help! Unfortunately, it gives me a #Value! error in C9 for me and I can't figure out why.

 

[shg]

The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter. That's what makes the curly braces appear.

 

[Jbone697]

Awesome, thanks again! I think I can work with this.

 

[shg]

You're welcome.

If yiou change the formula to this ...

=INDEX(StartCol, MATCH(1, INDEX((ShiftCol=ThisShift) * (LunchCol=ThisLunch), 0), 0))

... it can be confirmed with just Enter.