Check Another Sheet for Value across Multiple Columns

Hello @Paul Newcome ,

This is an interesting topic and it is similar to what I am currently working on.

Do you happen to have an update on this?

Your help will be greatly appreciated.

Cheers!

Can you clarify your specific scenario? (E.g. what your source sheet looks like, what you want to bring back into your new sheet). Screen captures would be helpful, but please block out sensitive data 🙂

Hello @Genevieve P. ,

Sheet 1 has the following columns:

Data is drawn from a form with each row representing a data set.

Second through third columns of Sheet 2 contains formula to determine the entries provided for each Property Name and expected to have the following results:

I was able to the get the count on the No. of Submissions by applying the formula on Column 2: COUNTIF({[Sheet 1] Property Names}, =[Property Name]@row).

Where [Sheet 1] Property Names encompasses columns Type A, Type B, and Type C.

However, I was unsuccessful in determining the value for Assigned Team for each row under Property Name using this formula: INDEX({[Sheet 1] Assigned Team}, MATCH([Property Name]@row, {[Sheet 1] Property Names}, 0)).

An empty value came out on the cell, though.

What do you think is wrong with the formula that I have applied?

Any help will be greatly appreciated.

Thanks for clarifying! This is really helpful.

What would you like to happen if there are more than one values in the source sheet? For example:

What I would do here is use 3 separate JOIN(COLLECT formulas, each looking at an individual Type column, like so:

=JOIN(COLLECT({Assigned Team}, {Type A}, [Property Name]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Assigned Team}, {Type B}, [Property Name]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Assigned Team}, {Type C}, [Property Name]@row), CHAR(10))

Then I'd put this in a multi-select column so that the multiple options show up:

Cheers,

Genevieve

Thanks @Genevieve P.

Hello again @Genevieve P.

I have applied the formula on the "No. of Submissions" column. However, when I need to get the aggregate (using the SUM function) on the said column, it returns no value.

I have modified the same column to make it as "=[Property Name]@row" and still returns no value.

May I be further guided on this.

Thank you

Can you post screen captures of your sheet and the current formula you're trying? (But please block out sensitive data)

Thanks for the quick reply @Genevieve P. .

In reference to the above screenshot, the formula applied on the yellow cells are similar to:

Taking the sum on selected cells (for testing purposes) gave a zero value.

If I use "+" on the values, it resulted to concatenation of the values.

Any thoughts for a workaround?

Thank you, this helps!

It looks like the JOIN formulas are bringing together the values so they're seen as text, not numerical. Can I ask why you've switched to JOIN from the COUNTIF formula above?

You should be able to add the results of a COUNTIF formula with other results to get a number. When you say that the formula is "similar" to the JOIN formula above, is it the same but referencing a different column? Or are you using COUNTIFs? This is the formula we'll want to look at so that you can eventually SUM the results. 🙂

I was able to find a workaround using the VALUE function (helper column).

Thank you, though :-)