Double OFFSET based on MATCH

[John Bates]

Hi friends,

I'm having a tough time with this problem. In the attached doc, you will notice 3 worksheets. The Paste! contains the data; Step6! is the location where I need some formula help; Categories! is the location from which we will be pulling values. All of this data is dynamic and changes daily (though the data structure does not change).

It may be best to run through an example. In cell Step6!B3, I need a formula that will do the following:

Search Paste!B3 for either "/Category" or "/Table" (if not found, then "0")
If either is found, then pull the text phrase directly before the "/"
Then lookup the column name on row Categories!2:2
Then vertically lookup the text phrase directly before the "/"
Offset to the column to the right directly under "Trans" and pull in the number

In my example we would get the following in Step6!B3 at each of these steps:
TRUE ---- Paste!B3 does contain "Category"
Lookup "GeoSegmentation Demographic Area" in Categories!2:2 ---- Categories!BC2
Lookup "phoenix (753)" in column Categories!BC ----Categories!BC17
Offset and return the number under "Trans" column ----Categories!BD17 = "16"

I hope that makes sense. I will need to drag this formula over to the right and down. Thank you soooo much! I've been working on this for over 2 hours and no luck.

Thanks,
John

[DonkeyOte]

You should look to avoid repetitive calcs.

Given the header is constant for all rows you should find this value once only for all rows and reference the result thereafter - this will help in this instance given you need to use the value twice over.

For sake of demo. using your file...

Step6!H1:
=MATCH(B$1,Categories!$1:$1,0)
copied across to L1
(obviously you may decide to relocate these values - this is just to demo concept)

Step6!B3:
=IFERROR(INDEX(Categories!$A:$BE,MATCH(TRIM(REPLACE(LEFT(Paste!B3,FIND("/",Paste!B3)-1),1,FIND(":",Paste!B3),"")),INDEX(Categories!$A:$BE,0,H$1),0),H$1+1),0)
copied down and across

above uses IFERROR which is exclusive to XL2007 and beyond

[DonkeyOte]

edit: the above won't work on the http's... modification required ... I will post back.

[NBVC]

Maybe:

Please Login or Register  to view this content.

copied down.

[NBVC]

Didn't see DO's posts before... but same problem regarding the HTTP.. if you copy across...

...since DO is working on something... I will let him handle it.

[DonkeyOte]

To be honest I think given the URL lengths in play you will struggle here - the MATCH will fail with strings exceeding 255 chars in length.

(presumably the heading for Column C should be Uniques)

[John Bates]

Thanks for all your help!!! I replaced the Referrers column with DemandGen Vehicle and it doesn't seem to be pulling in correctly. The character length caveat is fine to have with things like the referrer/URL strings.

Thanks again!!

[NBVC]

The table in Categories for DemandGen Vehicle is not setup in the same fashion as the table for GeoSegmentation Demographic Area.

In this case it finds the header match in column G.. and looks for Direct Load, etc. in column G.

You should be consistent in the setup of the tables...

[John Bates]

Perfect, thanks. I changed up the columns to make everything more consistent and it's working like a charm. Thanks so much again!!!

By the way, what do the brackets do within a function? (e.g., SEARCH({"Category","Table"},

Thanks again!!!

[NBVC]

Forms an array of values.