Two Criteria Lookup with Moving Starting Points

Hi all,

So I have been struggling with this problem for some time and I thought I had a fix, but it turns out, my approach was too complex and didn't work 100% o the time for some reason.

So, the business problem:
Each month Search, referred, and direct load change places as contributing the highest percentage of traffic to a website. EX:

Date	Date Indicator	Traffic Source 1	Traffic Source 2	Traffic Source 3	Traffic Source Percentage 1	Traffic Source Percentage 2	Traffic Source Percentage 3
Q1 2013	1	Referred	Search	Direct load	37%	32%	31%
Q2 2013	2	Search	Referred	Direct load	44%	29%	27%

Sample Data.xlsx
I need to return this months sources in order, the percentages for this month, and next to each of these numbers, last months percentage.

i.e.
Search 44% 32%
Referred 29% 37%
Direct load 27% 31%


The part I am struggling with is the previous months value. So essentially I have to look down using a vlookup (current month indicator -1), then in that row, look over to find "search", then go over three additional spaces from there, and return that percentage.

I tried implementing match and or Hlookup, but each need you to know where the first row is to begin your lookup.

Is there an easy way to do this? haha or even a complex way to do this?

Thanks!

Hi and welcome to the forum

I dont see how you can get ANY data for "this month" seeing as all you have is data for 2 quarters?

1st you need to make sure you have no leading/trailing spaces in your text (C3 has a trailing space)

This got a bit more complex than I thought, but try this...

for the 1st 1...
=INDEX($C$1:$E$10,ROW(A2)+1,MATCH(LARGE($F3:$H3,COLUMN(A$1)),$F3:$H3,0))&" "&TEXT(LARGE($F3:$H3,COLUMN(A$1)),"0%")&" "&TEXT(INDEX($F2:$H2,1,MATCH(INDEX($C$1:$E$10,ROW($A2)+1,MATCH(LARGE($F3:$H3,COLUMN(A$1)),$F3:$H3,0)),$C2:$E2,0)),"0%")
for the 2nd 1...
=INDEX($C$1:$E$10,ROW(B2)+1,MATCH(LARGE($F3:$H3,COLUMN(B$1)),$F3:$H3,0))&" "&TEXT(LARGE($F3:$H3,COLUMN(B$1)),"0%")&" "&TEXT(INDEX($F2:$H2,1,MATCH(INDEX($C$1:$E$10,ROW($A2)+1,MATCH(LARGE($F3:$H3,COLUMN(B$1)),$F3:$H3,0)),$C2:$E2,0)),"0%")
and for the 3rd...
=INDEX($C$1:$E$10,ROW(C2)+1,MATCH(LARGE($F3:$H3,COLUMN(C$1)),$F3:$H3,0))&" "&TEXT(LARGE($F3:$H3,COLUMN(C$1)),"0%")&" "&TEXT(INDEX($F2:$H2,1,MATCH(INDEX($C$1:$E$10,ROW($A2)+1,MATCH(LARGE($F3:$H3,COLUMN(C$1)),$F3:$H3,0)),$C2:$E2,0)),"0%")

ok whew, i *think* I have it

assuming that columns AC:AH in your real data relate to C:H in your dummy data, i changed the formula to this, and put it in DM8, copied across (twice) and down...
=INDEX($AC$6:$AE$30,ROW(A1)+1,MATCH(LARGE($AF8:$AH8,COLUMN(A$1)),$AF8:$AH8,0))&" "&TEXT(LARGE($AF8:$AH8,COLUMN(A$1)),"0%")&" "&TEXT(INDEX($AF$6:$AH7,ROW(A1)+1,MATCH(INDEX($AC$6:$AE$30,ROW($A1)+1,MATCH(LARGE($AF8:$AH8,COLUMN(A$1)),$AF8:$AH8,0)),$AC7:$AE7,0)),"0%")

If you want it/them anywhere else, cut/paste or move, dont just copy/paste

ok great what answer does that give you?