Joining two formulas together

[Westie102]

Hi everyone,

I have achieved so much from everyone's help on here, but I need to impose once more if possible for what I hope to be reasonably easy with some of the experience clearly hanging out here! I am so grateful for all your help so far. I have two formulas that work fine independently:

Priority - which returns data from the same array, based on its priority score, either "All Priorities" or Priority 1/2/3/4

=IF(F1="All Priorities",(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,MatrixScores!$I$1:$I$400=F1),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))

Borough - which returns data from a larger table on a different tab to display it more easily for users based on either "all boroughs" or a selected borough from the array:

=IF(G1="All Boroughs",(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,MatrixScores!$E$1:$E$400=G1),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))



I have now been asked to be able to pull the data back based on Priority selection (selected from cell F1) and then by borough (selected from cell G1) in the same place - like a multi-choice I guess.

I thought It would be an 'AND' statement in the formula, but for the life of me, I can not make it work, but that maybe my inexperience - I haven't been writing formulas for very long and seem to have been pushed straight into the deep end! I love learning, but I am struggling.

I would be grateful for any support, suggestions or ideas you may have.

Many thanks
Nicky

PS - I am using Excel 365/Desktop 2016

 

[braindiesel]

Re Multiple choice, you mean you want Cell F1 to have either the Priorities formula or Boroughs formula based on a choice?

In cell F1
=if(E1="P",<Priorities formula>,if(E1="B",<Boroughs formula>,""))

Something like that?
There are other approaches but would this work?

 

[Fluff]

Maybe
=IF(OR(F1="All Priorities",G1="All Boroughs"),(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,(MatrixScores!$I$1:$I$400=F1)+(MatrixScores!$E$1:$E$400=G1)),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))

 

[Westie102]

Re Multiple choice, you mean you want Cell F1 to have either the Priorities formula or Boroughs formula based on a choice?

In cell F1
=if(E1="P",<Priorities formula>,if(E1="B",<Boroughs formula>,""))

Something like that?
There are other approaches but would this work?

Thank-you braindiesel, unfortunately, the requirements are for both, but separately...

So the user gets to select the Priority (All Priorities, Priority 1, Priority 2, 3,4) in F1
And then they get to select the Borough (All boroughs, U, V, W, X, Y, Z..) in G1

So the results need to reflect both the selections and that's where I'm getting stuck.. Before I had them on different tabs, but of course some bright spark has requested they be together! (bless them ) I agree it would be a better solution - hence why I've spent most of the day trying to work it out.

hope that helps clarify?

 

[Westie102]

Maybe
=IF(OR(F1="All Priorities",G1="All Boroughs"),(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,(MatrixScores!$I$1:$I$400=F1)+(MatrixScores!$E$1:$E$400=G1)),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))

Thank-you Fluff, it hasn't quite worked, but I'm just trying to work with your idea

 

[Fluff]

In what way didn't it work?

 

[Westie102]

In what way didn't it work?

It's not updating when you change the priority or borough (F1 / G1)
So brings back as expected for All priorities and all boroughs, just not adapting to changes?

But, I've just changed the 'OR' to an "AND" and that seems to be working? - just doing some more testing

So, it must need an OR because it is now bringing back a combination of results! Oh... I feel so close.......

 

[Westie102]

In what way didn't it work?

Hi Fluff,
I have adapted your suggestion slightly to:

=IF(AND(F1="All Priorities",G1="All Boroughs"),(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,30,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$3:$AE$400,(MatrixScores!$I$3:$I$400=F1)+(MatrixScores!$E$3:$E$400=G1)),30,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))

The priority works fine and returns results based on the priority when 'all boroughs' selected
The boroughs work fine when 'all priorities' are selected

But I've still got a hic-up with the combination when it's in use

Is there a Simpler way to do this - I'm thinking If you select a priority, it must always be all boroughs and vice versa...
I think that would be fine with the data.

So maybe it's changing the formula to just read from G1 (list of Priorities and boroughs?). I'm thinking this may the best way forward

Thanks again for your help

 

[Fluff]

In what way?

 

[Westie102]

In what way?

Hi Fluff, apologies for the delay. I'm just wondering if it will be simpler (and more achievable) if I change my thinking to giving them one option - either by searching for 'all priorities' against a specific borough or a specific borough against all priorities.

So for example:

• Priority 1 from across all boroughs
• Borough X with all priorities (Preferably sorted 1-4!)
• Or everything - all priorities, all boroughs (sorted in Priority order)

I'll have a little play with this idea today, I'll probably be back asking for help again later