VLOOKUP to Gather Monthly Adoption Number based on Country

I am trying to find a way to create a metrics based on our monthly adoption numbers, this is the sheet that im trying to take the data from and then enter into the applicable fields in my metrics

I want to take if its Global, and if the month is ex: January, take the Online Adoption Rate total and copy it into my metrics under Global and the applicable month and this would be continued for each Country, and each month

This is my metrics

Is there an easy way to do this that isnt so complicated?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You would first need a helper column on the source sheet that brings the parent data down onto every row.

=PARENT([Primary Column]@row)

Then the formula that can be used to bring the data over for January would be:

=INDEX(COLLECT({Source Sheet January Column}, {Source Sheet Helper Column}, @cell = Countries@row, {Source Sheet Primary Column}, @cell = "Online Adoption Rate Total"), 1)

One thing to note here is that the parent rows in the source sheet should match the Countries column in the target sheet. Either that or a helper column in the target sheet where you can enter the value to match on in place of the Countries.

All comments

Paul Newcome

You would first need a helper column on the source sheet that brings the parent data down onto every row.

=PARENT([Primary Column]@row)

Then the formula that can be used to bring the data over for January would be:

=INDEX(COLLECT({Source Sheet January Column}, {Source Sheet Helper Column}, @cell = Countries@row, {Source Sheet Primary Column}, @cell = "Online Adoption Rate Total"), 1)

Cayla Davis

Ah HA!!! I had to do a few adjustments and got it to work! you are a genius; btw, is there any magical way to update the formula so you dont have to update each month manually for the {Source Sheet January Column}

=INDEX(COLLECT({Source Sheet January Column}, {Source Sheet Helper Column}, @cell = Countries@row , {Source Sheet Primary Column}, @cell = "Online Adoption Rate Total"), 1)

Paul Newcome

Happy to help. 👍

Technically there are a few different options, but they are either less reliable or it shifts the complexity / work. I'd be happy to go through some of the different options if you'd like, but my recommendation would be to just go with this and create the cross sheet references / update the formulas individually.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.