INDEX MATCH with IF Statement

WalkAlot

New Member
Joined
Mar 31, 2018
Messages
7
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?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sorry for wasting anyones time, I think I've solved it after a bit more reading with:

=INDEX(Mobile!A:H,MATCH(1,(Mobile!E:E=C3)*(Mobile!B:B=B$1),0),4)

If anyone see's this post could they just confirm that I've done the correct thing here. I seem to be getting the correct result now.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top