Add another criteria to this Index - Match - Large combo formula?

[RockandGrohl]

It's Friday Evening and my brain is melted, I can't think and this is providing me with errors which is incredibly frustrating.

{=INDEX(Dashboard!$D$4:$D$17143,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$17143=A3,Dashboard!$O$4:$O$17143),G3)=Dashboard!$O$4:$O$17143)*(Dashboard!$A$4:$A$17143=A3),),0))}

That's my formula, I need to add one more criteria.

Dashboard is where all the data is held. Column D is a list of Tour References which I am dragging back when it finds the highest score in Column O, this finds the highest score based on a range of cells 17,143 long which is column A.

So the current criteria is "If I can find A3 in Dashboard A4:A17143" and "Find the highest score" and return it.

I need to add another criteria which is "If in Dashboard 'column I' find D3"

When I do this, I get an error:

{=INDEX(Dashboard!$D$4:$D$17143,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$17143=A3,Dashboard!$O$4:$O$17143),G3)=Dashboard!$O$4:$O$17143)*(Dashboard!$A$4:$A$17143=A3)*(Dashboard!$I4:I$17143=D3),),0))}


Hope someone can help before what remains of my consciousness vapourises.

 

[James006]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

Hello,

Adding a criteria in your array formula is only modifying the Matrix of Trues and Falses ...

So just make sure your database does hold the results you are looking for ...

You can double-check with Filter ...

Hope this will help

 

[Aladin Akyurek]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

Possibly, control+shift+enter, not just enter;

=INDEX(Dashboard!$D$4:$D$17143,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$17143=A3,Dashboard!$O$4:$O$17143),G3)=Dashboard!$O$4:$O$17143)*(Dashboard!$A$4:$A$17143=A3)*(Dashboard!$I4:I$17143=D3),0),0))

 

[RockandGrohl]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

Possibly, control+shift+enter, not just enter;

=INDEX(Dashboard!$D$4:$D$17143,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$17143=A3,Dashboard!$O$4:$O$17143),G3)=Dashboard!$O$4:$O$17143)*(Dashboard!$A$4:$A$17143=A3)*(Dashboard!$I4:I$17143=D3),0),0))

That works, kind of:

Here's an explanation. This formula sits on the Ranking sheet and looks into the Dashboard.

On the ranking Sheet, A3 is the paper name, G3 is the rank (which the LARGE formula is tied to) and D3 is the advert type (which can be either UK, EU or Promo)

This formula is placed in H3 where it pulls through the tour number of the corresponding rank in G3.


On the dashboard page, in A:A I have the paper names, D:D are the tour references, I:I is the Tour Type and O:O is the score in numeric format.



This formula does work, but there's a problem. If I set the tour type from UK to EU, I get #NA , until I manually change the score of the best EU tour from being 10th best to 1st place, in which case the formula correctly lists the best EU tour date.


I thought my Large statement in the formula would only give me the largest score it can find the range. Cheers!

 

[RockandGrohl]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

Can't edit, but - the error message I'm getting is the "Value not Available" message. It seems like its struggling finding specifically the kth largest score for EU or Promo tours. How weird.

 

[Aladin Akyurek]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

Can't edit, but - the error message I'm getting is the "Value not Available" message. It seems like its struggling finding specifically the kth largest score for EU or Promo tours. How weird.

Care to post a small sample along with the expected result for that sample?

 

[RockandGrohl]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

Care to post a small sample along with the expected result for that sample?

Sure, I'll have to cut a lot of it down, but sure.

 

[RockandGrohl]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

Where do I go to upload again?

 

[RockandGrohl]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

https://ufile.io/yhe5s

Cheers

 

[RockandGrohl]

Re: Should be a simple one - how do I add another criteria to this Index - Match - Large combo formula?

{=INDEX(Dashboard!D4:D133,MATCH(LARGE(IF(Dashboard!$A$4:$A$133=Ranking!A3,IF(Dashboard!$I$4:$I$133=Ranking!D3,Dashboard!$O$4:$O$133)),Ranking!G3),Dashboard!$O$4:$O$133,0))}

I've got this so far... that kinda... sorta... looks like it might work, actually... You can change the value from cell D3 to be UK, EU or Promo and it seems to work.

I'm not sure what will happen when two tours have the same numerical rank, however.