I need formula Help!

https://community.smartsheet.com/discussion/comment/313134#Comment_313134

Ah! Actually, this is perfect.

If there is no match, you'll receive the #Invalid Value error. This means we can wrap the whole thing in an IFERROR statement!

=IFERROR(formula, formula if the first one errors)

or in your case:

=IFERROR(INDEX(COLLECT({Sheet - Employee Info Range 1}, {Sheet - Employee Info Range 2}, HAS(@cell, [Property]@row),{Sheet - Employee Info Range 3}, "Engagement Manager"), 1), INDEX(COLLECT({Sheet - Employee Info Range 1}, {Sheet - Employee Info Range 2}, HAS(@cell, Property@row), {Sheet - Employee Info Range 3}, CONTAINS("Engagement Manager", @cell)), 1))

Is this perhaps our final formula with the correct result?

All comments

It sounds like an INDEX(COLLECT formula would work in this instance!

An INDEX(COLLECT works like this:

=INDEX(COLLECT({Column to Return}, {Criteria Column 1}, [Criteria 1]@row, {Criteria Column 2}, [Criteria 2]@row), 1)

In your case, since one of your columns is a multi-select column, we'll want to use the HAS function to see if your column has that value in the cell, like so:

{Criteria Column 1}, HAS(@cell, [Criteria 1]@row)

Try something like this:

=INDEX(COLLECT({Contact Column}, {Property Name Column}, HAS(@cell, [Property Name]@row), {Title Column}, [Title Column]@row), 1)

If this doesn't help, it would be useful to see a screen capture of each sheet so we can identify what each of the column types are and how we can best help you, but please block out sensitive data.

Cheers,

Genevieve

Hi @Genevieve P.

Appreciate your help on this (and that fact that you could decode my question!)

I tried your formula but I think that last part [Title Column] threw me off. I don't have a title column in my Sheet A. I've attached screen shots of both sheets to hopefully provide more clarity!

Thanks so much for your help!

No problem! Let's see if we can clean this up for you.

You noted that the row in the source sheet needed to match a Property, however since there could be multiple rows, you can narrow this down even further with a second criteria.

It looks like you have a Title column in the source sheet. You can either match the content in this column across sheets by having an identical column in Sheet B and referencing a cell (which is why I had [Title Column]@row in my formula) or you could type the matching text "into quotes" to ask the formula to look for that specific text, like so:

=INDEX(COLLECT({Contact Column}, {Property Name Column}, HAS(@cell, [Property Name]@row), {Title Column}, "Matching Value"), 1)

So in your example screen capture:

=INDEX(COLLECT({Contact Column}, {Property Name Column}, HAS(@cell, [Property Name]@row), {Title Column}, "Master Tester"), 1)

Does that make sense?

@Genevieve P.

Yes that does make sense! Here is what I've typed in but I'm still getting an #INVALID REF error. Am I mistyping something?

=INDEX(COLLECT({Sheet - Employee Info Range 1}, {Sheet - Employee Info Range 2, HAS(@cell, [Property]@row),{Sheet - Employee Info Range 3}, "Sr Engagement Manager"), 1)

For Refence Range 1 on Sheet B is Employee Name, Range 2 is Properties, Range 3 is Title

So close! It looks like you're just missing a closing } after Range 2

=INDEX(COLLECT({Sheet - Employee Info Range 1}, {Sheet - Employee Info Range 2}, HAS(@cell, [Property]@row),{Sheet - Employee Info Range 3}, "Sr Engagement Manager"), 1)

Hi @Genevieve P

How did I miss that? This is so close to being perfect and since you're some kind of genius I have one last issue.

We have Engagement Manager and Sr Engagement Manager. I want it to return for either. When I use the direct quotes "Engagement Manager" I Get the correct response, but in an instance where there is a Sr Engagement Manager (and no Engagement Manager) I am getting an #Invalid Value. Which makes sense. I am pretty sure this is basic formula knowledge but I can't remember how to return the result if the cell contains Engagement Manger vs being an exact match. Any ideas here?

It's an easy miss- sometimes you just need a second set of eyes! 🙂

In regards to the last detail, there's a clue in how you phrased your question: "how to return the result if the cell contains Engagement Manger..."

We have a CONTAINS function that you can use for this! It's similar to HAS, but the structure is backwards. Try this:

=INDEX(COLLECT({Sheet - Employee Info Range 1}, {Sheet - Employee Info Range 2}, HAS(@cell, [Property]@row),{Sheet - Employee Info Range 3}, CONTAINS("Engagement Manager", @cell), 1)

Let me know if that does it, now.

Hi @Genevieve P.

I did try Contains before I asked I promise!

Weirdly enough it returns the Sr Engagement Anytime with that formula. I did have to add another ) after the last @cell is that means anything. Here's what I have in there:

=INDEX(COLLECT({Sheet - Employee Info Range 1}, {Sheet - Employee Info Range 2}, HAS(@cell, Property@row), {Sheet - Employee Info Range 3}, CONTAINS("Engagement Manager", @cell)), 1)

@Genevieve P.

After thinking about this. The issue is on Properties that both a Sr and an Engagement Manager it returns the first person in the list, I really need to only give the Sr when there is no Engagement. But if there is an Engagement it should return that.

I think that makes this way more complicated. 😞

Oops, this time I missed a parentheses! Yes, you would need the extra ) in there, that's the right spot.

CONTAINS will find a cell that contains that word, with or without the Sr. The INDEX is set to find the first match (which is what the 1 is at the end), so if you have both Sr. and one without Sr, the formula will bring back the first matching row out of the two. Does that make sense? Do you have something that defines when you want to bring back Sr Engagement vs. just Engagement?

https://community.smartsheet.com/discussion/comment/313134#Comment_313134

Ah! Actually, this is perfect.

If there is no match, you'll receive the #Invalid Value error. This means we can wrap the whole thing in an IFERROR statement!

=IFERROR(formula, formula if the first one errors)

or in your case:

Is this perhaps our final formula with the correct result?

@Genevieve P.

I think I found a work around. I just duplicated the original formula in a new Column so I have one for Engagement and one for Sr Engagement and then a third that uses IFError to pull the Sr if the Engagement returns an error.

I couldn't have gotten this done without you. Thank you so much for your time on this!

No problem at all! I'm glad we got there in the end. 🙂