I need formula Help!

Steve Krutsch · 12/08/21

I am trying to find a formula that will allow me to Reference another sheet and look in two columns (One is a multi select drop down, and one is a text column) and find the row that contains both of those values and return a third column (Contact) from the referenced sheet.

So Sheet A is where I want the formula to return the contact, and Sheet A also have a Property Name Column.

I want the formula to find a property name in a column in sheet B that matches the Property Name in the row with the formula, but since there will be multiple rows in Sheet B with that property name it also needs to match a Title that I can either add to sheet A or type into the formula.

If both the Property Name and Title Columns match in the same row in Sheet B return the contact from that row in sheet b.

I hope that makes sense. I am not sure how else I can show you these sheets to make it make sense. Any ideas on this?

Genevieve P. · 12/09/21

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?

Genevieve P. · 12/09/21

Hi @Steve Krutsch

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

Steve Krutsch · 12/09/21

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!

Sheet B.png

Sheet A.png

Genevieve P. · 12/09/21

Hi @Steve Krutsch

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?

Steve Krutsch · 12/09/21

@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

Genevieve P. · 12/09/21

Hi @Steve Krutsch

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)

Steve Krutsch · 12/09/21

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?

Genevieve P. · 12/09/21

Hi @Steve Krutsch

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.

Steve Krutsch · 12/09/21

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)

Steve Krutsch · 12/09/21

@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. 😞

Genevieve P. · 12/09/21

Hi @Steve Krutsch

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?

Genevieve P. · 12/09/21

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?

Steve Krutsch · 12/09/21

@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!

Genevieve P. · 12/09/21

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

Steve Krutsch · 12/09/21

https://community.smartsheet.com/discussion/comment/313136#Comment_313136

Your way it better than my workaround. Can't thank you enough! Works Perfectly!

I need formula Help!

Best Answer

Answers

Help Article Resources

Categories