For reporting purposes, when you export data from Google Analytics, the information is not in the format or structure that you need. Google Analytics is a little limited on the reporting functionality and often requires some manipulation in Microsoft Excel to massage the data in the the format you need.
Often I am trying to find a text string within a dataset without being able to match the entire string.
What I am trying to do is determine if a value from a list exists in a range of columns and return a value from a corresponding column.
In my list from sheet 1 column A I have values like:
U04370
W71191
X770DJ
In sheet 2 I will have in column A:
TESTING INC #U04370 / JMO / BLISH-MIZE
TESTING INC #X848MM/RADIUS /WEGMAN’S FOOD
TESTING INC #W71191/INFOL/FIELD CONTAINER
TESTING INC #X770DJ/PROPH/MAGNUS INDUSTRIES
TESTING INC #X9950H/RUSH /ODOMS TENNESSEE PRIDE
and in sheet 2 Column B I will have
28832
46375
47524
49119
49166
I want to return to sheet 1 column B the value from sheet 2 column B when sheet 1 column A is contained in sheet 2 column A.
This function helps to solve that problem
=INDEX(Sheet2!$B$2:$B$6,MATCH(“*”&A2&”*”,Sheet2!$A$2:$A$6,0))
where “A2″ corresponds to the cell of the text string that you are trying to match. This function only works if there is only one matching cell in Sheet 2, Column A. If there are multiple instances of the text string in Sheet 2, Column A, then another function will have to be used for accurate results.
I really appreciate that you keep coming back to my site, it's loyal people like you that make what I do worth it. If you have any questions feel free to email me. Oh and since you obviously like our site it'd be great if you could share it with a friend... They'll love you for it!
