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.

 

[RockandGrohl]

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

I need to bump this as it's not working perfectly.

As predicted, there's issues where two tours have the same numerical rank, for example if I am looking for 4 UK tours, from 1st to 4th place, and there is an EU tour with the same rank as the UK 3rd place tour, it will show the EU tour in.

Anybody have any ideas? I need to Index-Match a result, based on the largest result of two given criteria (Paper name is the same, tour type is the same)

Thanks.

 

[Aladin Akyurek]

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

I need to bump this as it's not working perfectly.

As predicted, there's issues where two tours have the same numerical rank, for example if I am looking for 4 UK tours, from 1st to 4th place, and there is an EU tour with the same rank as the UK 3rd place tour, it will show the EU tour in.

Anybody have any ideas? I need to Index-Match a result, based on the largest result of two given criteria (Paper name is the same, tour type is the same)

Thanks.

Guessing...

In G3 of Ranking control+shift+enter, not just enter:

=MAX(IF(Dashboard!$A$4:$A$17143=$A3,IF(Dashboard!$I4:I$17143=$D3,Dashboard!$O$4:$O$17143)))

In H3 of Ranking control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX(Dashboard!$D$4:$D$17143,SMALL(IF(Dashboard!$A$4:$A$17143=$A3,IF(Dashboard!$I4:I$17143=$D3,IF(Dashboard!$O$4:$O$17143=Ranking!$G3,ROW(Dashboard!$D$4:$D$17143)-ROW(INDEX(Dashboard!$D$4:$D$17143,1,1))+1))),COLUMNS($H$3:H$3))),"")

 

[RockandGrohl]

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

Guessing...

In G3 of Ranking control+shift+enter, not just enter:

=MAX(IF(Dashboard!$A$4:$A$17143=$A3,IF(Dashboard!$I4:I$17143=$D3,Dashboard!$O$4:$O$17143)))

In H3 of Ranking control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX(Dashboard!$D$4:$D$17143,SMALL(IF(Dashboard!$A$4:$A$17143=$A3,IF(Dashboard!$I4:I$17143=$D3,IF(Dashboard!$O$4:$O$17143=Ranking!$G3,ROW(Dashboard!$D$4:$D$17143)-ROW(INDEX(Dashboard!$D$4:$D$17143,1,1))+1))),COLUMNS($H$3:H$3))),"")

Thanks for this - we ran out of time this week so have had to do the selections manually.. 400 selections which take 3-5 mins each = 26 hours work

I'll try on Friday, then I'm on hols for 2 weeks (if this Hurricane Florence doesn't disrupt)

Thanks for your help so far

 

[RockandGrohl]

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



Do you think it's better to use column B in Dashboard as a metric for measuring the score as it only contains a unique rank, whereas "Score" can have multiple 62.8's for instance. B, or "Rank" column has numbers ascending from 1 to 75 with no duplicates or misses.

 

[RockandGrohl]

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

Hi Aladin,

It's not quite what I need, this returns the best result for each paper which is fine, but I need to do the 2nd best, 3rd best and sometimes 4th best result. In addition it requires an extra helper column (G3)

The way my ranking page is laid out is as such:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Paper[/TD]
[TD]Paper Helper[/TD]
[TD]Template Size[/TD]
[TD]Choice[/TD]
[TD]Group[/TD]
[TD]Template[/TD]
[TD]# Tours[/TD]
[TD]Rank[/TD]
[TD]Tourno[/TD]
[TD]Tour[/TD]
[TD]Rank[/TD]
[TD]Tourno[/TD]
[TD]Tour[/TD]
[TD]Rank[/TD]
[TD]Tourno[/TD]
[TD]Tour[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ashbourne News - Fri[/TD]
[TD]Ashbourne News[/TD]
[TD]25x4 (1)[/TD]
[TD]UK[/TD]
[TD]TRN[/TD]
[TD]25x4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]177249[/TD]
[TD]Thursford CS[/TD]
[TD]2[/TD]
[TD]116C74[/TD]
[TD]Isle of Wight HP[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

On my Dashboard page, the columns are laid out like:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Paper[/TD]
[TD]Rank[/TD]
[TD]TourNo[/TD]
[TD]Tour[/TD]
[TD]Vehicles[/TD]
[TD]Last Selected[/TD]
[TD]Price[/TD]
[TD]Type[/TD]
[TD]Pax[/TD]
[TD]Last Pax[/TD]
[TD]ROI[/TD]
[TD]Tour Date[/TD]
[TD]Frequency[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ashbourne News[/TD]
[TD]12[/TD]
[TD]3Y2023[/TD]
[TD]Christmas in Benidorm[/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]600[/TD]
[TD]EU[/TD]
[TD]49[/TD]
[TD]42[/TD]
[TD]3.45[/TD]
[TD]22/12/2018[/TD]
[TD]2[/TD]
[TD]31.3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ashbourne News[/TD]
[TD]1[/TD]
[TD]177249[/TD]
[TD]Thursford CS[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]250[/TD]
[TD]UK[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]7.80[/TD]
[TD]07/12/2018[/TD]
[TD]1[/TD]
[TD]60.1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ashbourne News[/TD]
[TD]2[/TD]
[TD]116C74[/TD]
[TD]Isle of Wight HP[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]319[/TD]
[TD]UK[/TD]
[TD]12[/TD]
[TD]7[/TD]
[TD]6.78[/TD]
[TD]28/11/2018[/TD]
[TD]1[/TD]
[TD]59[/TD]
[/TR]
</tbody>[/TABLE]


Right so in Ranking I3, this would be where the Index-Match sits. It looks in Dashboard A for a Paper Name match, then in Dashboard H for a Tour Type match, then it looks up and down Dashboard B for the highest result which fits the aforementioned criteria. When it finds the highest score it can, it returns the result in Ranking I3, where the rest of the tour information is passed along.

My current formula is as follows:

{=IFERROR(INDEX(Dashboard!$C$4:$C$18295,MATCH(LARGE(IF(Dashboard!$A$4:$A$18295=A3,IF(Dashboard!$H$4:$H$18295=D3,Dashboard!$N$4:$N$18295)),H3),Dashboard!$N$4:$N$18295,0)),"No EU")}

This mostly works, it definitely does something, however it trips up whenever it comes across a duplicate score and then breaks its own ruleset and returns me EU tours in UK criteria, or two identical tours which shouldn't happen at all.

Thanks

 

[Aladin Akyurek]

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

1. Try to post a small sample without irrelevant data (columns).

2. Try not to discuss the problem by means of Excel formulas!.. Show input and the output you desire to get from that input.

3. Are you trying to get a Top N result where N is say 3? This is a guess...

 

[RockandGrohl]

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

1. Try to post a small sample without irrelevant data (columns).

2. Try not to discuss the problem by means of Excel formulas!.. Show input and the output you desire to get from that input.

3. Are you trying to get a Top N result where N is say 3? This is a guess...

Hi Aladin,

My sample is posted near the bottom of the first page.

You're correct, I'm trying to get a top N result when it satisfies both criteria of paper name and tour type. Whether that tour is ranked 1st, 12th, or 75th, I want the best rank of the matching criteria.

I've discovered the problem with my new formula on the reply above yours is that where a score (column N) has a duplicate score, it throws it off. Someone suggested in another thread to do this:

=N4&COUNTIF($N$4:N4,N4)/100

To give a distinct score, so 60.40 because 60.401 and another 60.40 might become 60.405

This would be satisfactory, however, there's some kind of error and it's giving me 60.40.01 and turning 10.00 into 100.01 which obviously is no good.

If you know a quick and dirty way to to give a unique result? Currently my formula in Column N is =MAX(0,SUM(P4:AA4))

This MAX function prevents negative numbers throwing errors in the LARGE segment of the Index-Match-Criteria lookup. It would be good to augment this MAX + SUM function with another function to make the score distinct - =MAX(0,SUM(P4:AA4,ROW((N4)/100)))? This gives an error..

 

[Aladin Akyurek]

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

We are still discussing formulas!..

If N4 houses a score on which the Top N is based, then I'd expect:

COUNTIFS($N$4:N4,N4)