yogi_Help With LookUp/Match and Split

Google Spreadsheet   Post  #2151

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-10-2017

question by giorobert88:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/UMjEdZqx5bU;context-place=mydiscussions
Help with lookup/match and split

Hi all,

I have been creating a spreadsheet which feeds directly from a Trello API.  One of the columns returns all of the tags for a given customer in a comma seperated list and I am looking for a way to look up the most recent and then only return the details to the right of the "unit:" tag before the next seperator (,). 

So far I have found a way to lookup the latest version:

=arrayformula(if(len(A2:A)=0,iferror(1/0),iferror(vlookup(A2:A,Log!B2:C,{1}*sign(row(A2:A)),FALSE))))

And a way to split:

=TRANSPOSE(SPLIT(JOIN("," , Log!C2:C), ",", FALSE))), 1, TRUE)

However, I am struggling to combine the 2, filter for only the data containing unit: and then return the data before the next comma. A copy of the spreadsheet can be found here:

https://docs.google.com/spreadsheets/d/14MSrBqZ9ye372_NFXUXgnBW9LJCbMatRVjHGUht12Uw/edit?usp=sharing

The log shows the raw output from the API. The Summary sheets shows the table I am trying to achieve. What I would like to return in cell B2:B4 is S5,S6,S5 therefore is looking up the latest activity per customer and returning the data following "Unit:" before the next , seperator.

Hopefully that makes sense. Any help would be much appreciated.

Thanks in advance.