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

Page TypeCategoryTemplateStatic PagesVisitsConversionRevenue: TotalUnits: Total
PromotionalWhite GoodsN/ACooker Sale893.25%11725.0020909
RangeSportsN/AExercise-Bikes874.20%12720.0021927
BrandWhite GoodsWorld CupHotpoint763.80%41635.005378
BrandSportsBronzeFitbit767.10%26623.002943
Product TypeWhite GoodsN/AWashine-Machines735.80%38715.005150
Product TypeSportsN/ATrackers732.40%31893.008802
RangeSportsN/AFootball726.75%17848.002927
BrandWhite GoodsN/ABrands721.89%37266.003227
RangeSportsSilverBadminton694.33%15944.005999
RangeWhite GoodsDryers596.43%34403.005226
RangeSportsGoldBoxing565.08%17264.008765
Product TypeWhite GoodsWhite-Goods-Landing543.66%20550.005557
Product TypeWhite GoodsN/AWasher-Dryers377.53%24159.006781
RangeSportsN/ABikes342.67%20246.009755
RangeSportsGoldIndoor-Training334.10%25566.001755
PromotionalWhite GoodsN/Adeals325.77%35156.0025633
ArticleSportsNutrition303.48%19536.001065
ArticleWhite GoodsBronzeWashing-Machine-Care227.24%35994.008188
RangeWhite GoodsGoldDishwashers214.61%25828.00956

<tbody>
</tbody>


Output Table

White Goods
m-CommVisitsRevenue
1Cooker Sale89Dishwashers£42,344.00
2Hotpoint76Washing-Machine-Care£37,394.00
3Washine-Machines73Hotpoint£34,382.00
4Football72deals£30,857.00
5Dryers59Washine-Machines£30,320.00
6White-Goods-Landing54Dryers£27,485.00
7Washer-Dryers37White-Goods-Landing£27,210.00
8deals32Washer-Dryers£24,770.00
9Washing-Machine-Care22Brands£19,895.00
10Dishwashers21Cooker Sale£12,774.00

<tbody>
</tbody>


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?
 

WalkAlot

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

Forum statistics

Threads
1,081,703
Messages
5,360,747
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top