Hi all,
So I'm trying to run an INDEX MATCH against the nuber of visits that some of my web pages have received and view the top 10 by category. The issue I have is that I've found that some of the pages have received the same number of visits so when running the INDEX MATCH it's returning the 1st vale that is seen against the corresponding number of visits. I'm assuming that I'm going to need to add an IF statement to my formula.
I would run a Pivot Table but I'm trying to develop a full dashboard that'll include lots of other information.
Source Data
[TABLE="class: grid, width: 664"]
<tbody>[TR]
[TD="width: 83"]Page Type[/TD]
[TD="width: 83"]Category[/TD]
[TD="width: 69"]Template[/TD]
[TD="width: 150"]Static Pages[/TD]
[TD="width: 72"]Visits[/TD]
[TD="width: 62"]Conversion[/TD]
[TD="width: 82"]Revenue: Total[/TD]
[TD="width: 62"]Units: Total[/TD]
[/TR]
[TR]
[TD]Promotional[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Cooker Sale[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]3.25%[/TD]
[TD="align: right"]11725.00[/TD]
[TD="align: right"]20909[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Exercise-Bikes[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]4.20%[/TD]
[TD="align: right"]12720.00[/TD]
[TD="align: right"]21927[/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]White Goods[/TD]
[TD]World Cup[/TD]
[TD]Hotpoint[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]3.80%[/TD]
[TD="align: right"]41635.00[/TD]
[TD="align: right"]5378[/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]Sports[/TD]
[TD]Bronze[/TD]
[TD]Fitbit[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]7.10%[/TD]
[TD="align: right"]26623.00[/TD]
[TD="align: right"]2943[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Washine-Machines[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]5.80%[/TD]
[TD="align: right"]38715.00[/TD]
[TD="align: right"]5150[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Trackers[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]2.40%[/TD]
[TD="align: right"]31893.00[/TD]
[TD="align: right"]8802[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Football[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6.75%[/TD]
[TD="align: right"]17848.00[/TD]
[TD="align: right"]2927[/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Brands[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]1.89%[/TD]
[TD="align: right"]37266.00[/TD]
[TD="align: right"]3227[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]Silver[/TD]
[TD]Badminton[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]4.33%[/TD]
[TD="align: right"]15944.00[/TD]
[TD="align: right"]5999[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]White Goods[/TD]
[TD][/TD]
[TD]Dryers[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]6.43%[/TD]
[TD="align: right"]34403.00[/TD]
[TD="align: right"]5226[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]Gold[/TD]
[TD]Boxing[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]5.08%[/TD]
[TD="align: right"]17264.00[/TD]
[TD="align: right"]8765[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]White Goods[/TD]
[TD][/TD]
[TD]White-Goods-Landing[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]3.66%[/TD]
[TD="align: right"]20550.00[/TD]
[TD="align: right"]5557[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Washer-Dryers[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]7.53%[/TD]
[TD="align: right"]24159.00[/TD]
[TD="align: right"]6781[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Bikes[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2.67%[/TD]
[TD="align: right"]20246.00[/TD]
[TD="align: right"]9755[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]Gold[/TD]
[TD]Indoor-Training[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]4.10%[/TD]
[TD="align: right"]25566.00[/TD]
[TD="align: right"]1755[/TD]
[/TR]
[TR]
[TD]Promotional[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]deals[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]5.77%[/TD]
[TD="align: right"]35156.00[/TD]
[TD="align: right"]25633[/TD]
[/TR]
[TR]
[TD]Article[/TD]
[TD]Sports[/TD]
[TD][/TD]
[TD]Nutrition[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]3.48%[/TD]
[TD="align: right"]19536.00[/TD]
[TD="align: right"]1065[/TD]
[/TR]
[TR]
[TD]Article[/TD]
[TD]White Goods[/TD]
[TD]Bronze[/TD]
[TD]Washing-Machine-Care[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]7.24%[/TD]
[TD="align: right"]35994.00[/TD]
[TD="align: right"]8188[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]White Goods[/TD]
[TD]Gold[/TD]
[TD]Dishwashers[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]4.61%[/TD]
[TD="align: right"]25828.00[/TD]
[TD="align: right"]956[/TD]
[/TR]
</tbody>[/TABLE]
Output Table
[TABLE="class: grid, width: 544"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 127"]White Goods
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 146"][/TD]
[TD="width: 79"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]m-Comm[/TD]
[TD]Visits[/TD]
[TD][/TD]
[TD][/TD]
[TD]Revenue
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cooker Sale[/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD]Dishwashers[/TD]
[TD="align: right"]£42,344.00
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Hotpoint[/TD]
[TD="align: right"]76[/TD]
[TD][/TD]
[TD]Washing-Machine-Care[/TD]
[TD="align: right"]£37,394.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Washine-Machines[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[TD]Hotpoint[/TD]
[TD="align: right"]£34,382.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Football[/TD]
[TD="align: right"]72[/TD]
[TD][/TD]
[TD]deals[/TD]
[TD="align: right"]£30,857.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dryers[/TD]
[TD="align: right"]59[/TD]
[TD][/TD]
[TD]Washine-Machines[/TD]
[TD="align: right"]£30,320.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]White-Goods-Landing[/TD]
[TD="align: right"]54[/TD]
[TD][/TD]
[TD]Dryers[/TD]
[TD="align: right"]£27,485.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Washer-Dryers[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD]White-Goods-Landing[/TD]
[TD="align: right"]£27,210.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]deals[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD]Washer-Dryers[/TD]
[TD="align: right"]£24,770.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Washing-Machine-Care[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]Brands[/TD]
[TD="align: right"]£19,895.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Dishwashers[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]Cooker Sale[/TD]
[TD="align: right"]£12,774.00[/TD]
[/TR]
</tbody>[/TABLE]
To return the visit number I'm using:
=LARGE(IF(Mobile!B:B=B$1,Mobile!E:E),1)
To look up the relevant page name, I'm trying:
=IF(Mobile!B:B=B1,INDEX(Mobile!$D:$D,MATCH($C3,Mobile!$E:$E,0)))
But this is just returning 'FALSE'
Could someone tell me how I include the IF as part of the INDEX MATCH please?
So I'm trying to run an INDEX MATCH against the nuber of visits that some of my web pages have received and view the top 10 by category. The issue I have is that I've found that some of the pages have received the same number of visits so when running the INDEX MATCH it's returning the 1st vale that is seen against the corresponding number of visits. I'm assuming that I'm going to need to add an IF statement to my formula.
I would run a Pivot Table but I'm trying to develop a full dashboard that'll include lots of other information.
Source Data
[TABLE="class: grid, width: 664"]
<tbody>[TR]
[TD="width: 83"]Page Type[/TD]
[TD="width: 83"]Category[/TD]
[TD="width: 69"]Template[/TD]
[TD="width: 150"]Static Pages[/TD]
[TD="width: 72"]Visits[/TD]
[TD="width: 62"]Conversion[/TD]
[TD="width: 82"]Revenue: Total[/TD]
[TD="width: 62"]Units: Total[/TD]
[/TR]
[TR]
[TD]Promotional[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Cooker Sale[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]3.25%[/TD]
[TD="align: right"]11725.00[/TD]
[TD="align: right"]20909[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Exercise-Bikes[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]4.20%[/TD]
[TD="align: right"]12720.00[/TD]
[TD="align: right"]21927[/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]White Goods[/TD]
[TD]World Cup[/TD]
[TD]Hotpoint[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]3.80%[/TD]
[TD="align: right"]41635.00[/TD]
[TD="align: right"]5378[/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]Sports[/TD]
[TD]Bronze[/TD]
[TD]Fitbit[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]7.10%[/TD]
[TD="align: right"]26623.00[/TD]
[TD="align: right"]2943[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Washine-Machines[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]5.80%[/TD]
[TD="align: right"]38715.00[/TD]
[TD="align: right"]5150[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Trackers[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]2.40%[/TD]
[TD="align: right"]31893.00[/TD]
[TD="align: right"]8802[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Football[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6.75%[/TD]
[TD="align: right"]17848.00[/TD]
[TD="align: right"]2927[/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Brands[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]1.89%[/TD]
[TD="align: right"]37266.00[/TD]
[TD="align: right"]3227[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]Silver[/TD]
[TD]Badminton[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]4.33%[/TD]
[TD="align: right"]15944.00[/TD]
[TD="align: right"]5999[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]White Goods[/TD]
[TD][/TD]
[TD]Dryers[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]6.43%[/TD]
[TD="align: right"]34403.00[/TD]
[TD="align: right"]5226[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]Gold[/TD]
[TD]Boxing[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]5.08%[/TD]
[TD="align: right"]17264.00[/TD]
[TD="align: right"]8765[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]White Goods[/TD]
[TD][/TD]
[TD]White-Goods-Landing[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]3.66%[/TD]
[TD="align: right"]20550.00[/TD]
[TD="align: right"]5557[/TD]
[/TR]
[TR]
[TD]Product Type[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]Washer-Dryers[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]7.53%[/TD]
[TD="align: right"]24159.00[/TD]
[TD="align: right"]6781[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]N/A[/TD]
[TD]Bikes[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2.67%[/TD]
[TD="align: right"]20246.00[/TD]
[TD="align: right"]9755[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]Sports[/TD]
[TD]Gold[/TD]
[TD]Indoor-Training[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]4.10%[/TD]
[TD="align: right"]25566.00[/TD]
[TD="align: right"]1755[/TD]
[/TR]
[TR]
[TD]Promotional[/TD]
[TD]White Goods[/TD]
[TD]N/A[/TD]
[TD]deals[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]5.77%[/TD]
[TD="align: right"]35156.00[/TD]
[TD="align: right"]25633[/TD]
[/TR]
[TR]
[TD]Article[/TD]
[TD]Sports[/TD]
[TD][/TD]
[TD]Nutrition[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]3.48%[/TD]
[TD="align: right"]19536.00[/TD]
[TD="align: right"]1065[/TD]
[/TR]
[TR]
[TD]Article[/TD]
[TD]White Goods[/TD]
[TD]Bronze[/TD]
[TD]Washing-Machine-Care[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]7.24%[/TD]
[TD="align: right"]35994.00[/TD]
[TD="align: right"]8188[/TD]
[/TR]
[TR]
[TD]Range[/TD]
[TD]White Goods[/TD]
[TD]Gold[/TD]
[TD]Dishwashers[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]4.61%[/TD]
[TD="align: right"]25828.00[/TD]
[TD="align: right"]956[/TD]
[/TR]
</tbody>[/TABLE]
Output Table
[TABLE="class: grid, width: 544"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 127"]White Goods
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 146"][/TD]
[TD="width: 79"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]m-Comm[/TD]
[TD]Visits[/TD]
[TD][/TD]
[TD][/TD]
[TD]Revenue
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cooker Sale[/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD]Dishwashers[/TD]
[TD="align: right"]£42,344.00
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Hotpoint[/TD]
[TD="align: right"]76[/TD]
[TD][/TD]
[TD]Washing-Machine-Care[/TD]
[TD="align: right"]£37,394.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Washine-Machines[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[TD]Hotpoint[/TD]
[TD="align: right"]£34,382.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Football[/TD]
[TD="align: right"]72[/TD]
[TD][/TD]
[TD]deals[/TD]
[TD="align: right"]£30,857.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dryers[/TD]
[TD="align: right"]59[/TD]
[TD][/TD]
[TD]Washine-Machines[/TD]
[TD="align: right"]£30,320.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]White-Goods-Landing[/TD]
[TD="align: right"]54[/TD]
[TD][/TD]
[TD]Dryers[/TD]
[TD="align: right"]£27,485.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Washer-Dryers[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD]White-Goods-Landing[/TD]
[TD="align: right"]£27,210.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]deals[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD]Washer-Dryers[/TD]
[TD="align: right"]£24,770.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Washing-Machine-Care[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]Brands[/TD]
[TD="align: right"]£19,895.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Dishwashers[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]Cooker Sale[/TD]
[TD="align: right"]£12,774.00[/TD]
[/TR]
</tbody>[/TABLE]
To return the visit number I'm using:
=LARGE(IF(Mobile!B:B=B$1,Mobile!E:E),1)
To look up the relevant page name, I'm trying:
=IF(Mobile!B:B=B1,INDEX(Mobile!$D:$D,MATCH($C3,Mobile!$E:$E,0)))
But this is just returning 'FALSE'
Could someone tell me how I include the IF as part of the INDEX MATCH please?