Multiple formula for single cells? Excel 2010

[BobbyG79]

Hi all,

I'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")

The following formula works in the first instance:

=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)

What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:

Variable 1:
Product Type A1
Product Type A2
Product Type B1
Product Type B2

Variable 2:
Internal
External

Variable 3:
Existing
New


Or am I better using a VLOOKUP or something???!!

Thanks,

Bob

[AZ-XL]

Hi
If the required Variable is text then
=IFERROR(INDEX(Table2[Var4],MATCH(1,(Table2[Var1]=I1)*(Table2[Var2]=I2)*(Table2[Var3]=I3),0)),"") Ctrl+Shift+Enter

But if the required variable is number then its easier
=SUMIFS(Table2[Var5],Table2[Var1],I1,Table2[Var2],I2,Table2[Var3],I3)

Simulated in excel file. Answers are in yellow cells
Hope that is what you want

[BobbyG79]

Thanks for your help AZ, I actually decided to use a pivot table in the end, once I'd worked out how to condition my raw data this was the obvious choice.

Cheers,

Bob