Joining two formulas together

Westie102

New Member
Joined
Feb 3, 2019
Messages
18
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2019
Messages
18
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

New Member
Joined
Feb 3, 2019
Messages
18

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
In what way didn't it work?
 

Westie102

New Member
Joined
Feb 3, 2019
Messages
18

ADVERTISEMENT

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.......
 
Last edited:

Westie102

New Member
Joined
Feb 3, 2019
Messages
18
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
In what way?
 

Westie102

New Member
Joined
Feb 3, 2019
Messages
18
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 ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,521
Messages
5,548,544
Members
410,847
Latest member
shaun32
Top