Extract values based on three (3) criteria

Looking for a formula to extract values when three (3) conditions are selected/met. For example, If you select Sky in B2, East in A2 and All[2001,2002,2003] in C2, formula should extract all values associated with Sky. If I select All[Blue,Average Sales,Gas milage,Sky] in B2 and All[2001,2002,2003] in C2, formula should extract values for each option. See


Example:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have used E1 to H2 to define search parameters for Variables and year using formula as below


=IF(ISNUMBER(SEARCH("Blue",$B$2)),"Blue","")

I have used the following to extract the data;

=IFERROR(INDEX(Sales!$H$2:$H$100,MATCH(1,(Sales!$B$2:$B$100=B$5)*(Sales!$A$2:$A$100=$E$2),0)),"")

Entered with Ctrl + Shift +Enter

Change RED to select required data and Green for year.

Change the selection and you will see the results updated.

ALL formulae are set up in the attached sheet.

JohnTopley: formula needs adjustment. I do not want the whole table to show each parameter. Let say I select Average Sales, All[2001,2002,2003] and Blue, I want the formula to only show average sales table with all three years

Please Login or Register  to view this content.

Thanks

Much more complicated as you will a complex formula for each permutation: understand your requirement.

I do not use them put perhaps a pivot table is a better solution if you want this degree of flexibility.

micope21: No. just 3 years

Thanks

Compromise solution?

Hi bjnockle nice to see u again, pls find the file attach, hope the best.....

azuma my friend, thanks for looking at the attached. Not able to open/download the file. Can you reattach it? I am getting an error message. File format or extension is not valid.

Thanks.

Outstanding solution JohnTopley. I can work with this. Would like to view azumi's solution for comparison before marking the problem solved. Thanks

Hi bjnockle,
John Topley suggested a pivot table in #4.
Please see attached Pivot Table option.
After you add more information to the sales sheet you will need to refresh the pivot tables.
Regards
peterrc

peterrc: not zeroing in on Pivot Table.

Thanks.

Very neat solution!

wow solution! works like a charm! You are a genius! thanks a million

Hi bjnockle

my mistake, im upload it again