Formula looking at column information

I have a vlookup formula pulling in information from another sheet into a Pivot Table.

In the Utilization column I need the breakdown from the columns to the right that match the categories in the Nominated to: column.

Which if it's one category I have a formula that's working,

=[Nominated to:]@row + " - " + [Sports Med - Event Count]2

but it's taking time to build them all out and in most cases there is more than 1 category in the Nominated to: column

I reached out to support and they gave the following solution, but it's pulling the Grand Totals column number and assigning it to both Nominated to: categories

=SUBSTITUTE([Nominated To:]@row, CHAR(10), " - " + [Grand Totals]@row + ", ") + " - " + [Grand Totals]@row

Example:

But, it may be split - what I'd like is for the Utilization column to look at all the columns to the right and then report the actuals where there is information with that matches the Category from the Nominated to: column and the Number from the corresponding column.

What I'd like this to look like is this:

Any assistance on if this is possible and how to achieve it would be appreciated!

Find more posts tagged with

Accepted answers

All comments

Leibel S

@Deb Allen

Where is the [Nominated To:] and [Grand Totals] columns' information coming from?

Deb Allen

The [Nominated To:] is a vlookup from another sheet, and [Grand Totals] is looking at the rows to the right and is pulling in from a summary sheet but are also in the corresponding columns to the right of the [Utilization] column

Leibel S

Is the number associated with each '[Nominated To:]' stored together with the '[Nominated To:]'?

Deb Allen

It is in the reference sheet, but as the total not the breakdown.

Would that be easier? I could pull a reference sheet with the breakdown too, I think.

Leibel S

Not clear on your setup, but in general your process for this should be something along these lines:

Wherever the [Nominated To:] is stored should be another column (say: [Nominated Details]) that has a formula along these lines:

=[Nominated To:]@row +" - " + the number wherever you have it

Then in your pivot sheet you would do a join/collect on this column above

=JOIN(COLLECT({Nominated Details Range}, Here you would put whatever range, then criteria you put in your VLOOKUP formula), CHAR(10))

These formula are incomplete but the process is good.

Deb Allen

So the Grand Total is the sum of the columns to the to right in the same sheet.

If that helps. I will try the formula you suggested!

Deb Allen

The formula isn't working, but the numbers I need for the various Nominated to: columns are all to the right of the utilization. Is there a way to reference those cells per row and match to the Nominated to: column?

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.