Help with INDEX MATCH COLLECT formula

jschumacher

Hi all,

I'm having trouble with a lookup-type function returning the wrong values.

We have our base sheet called the "Deal Tracker" where we keep tabs on the various deals that we consider (we're an investment firm). When entering a new deal, we always collect a Contact Name of the person who brought us the deal. We often receive multiple deals from the same person.

We want to leverage this data to make sure we're consistently keeping in touch with valuable contacts who bring us deals. The end result should be the following columns: Contact Name | Most Recent Deal from Contact | Date of Most Recent Deal | # Deals 2018 | # Deals 2019

We're using the Pivot app to collect # deals per contact as well as the comprehensive list of all contacts.

The problem I'm having is finding the most recent deal for each contact. It works fine for many of the contacts, but it pulls incorrect deals for other contacts and I don't know how to begin to figure out why

The data in the base sheet is not sorted and we really do not want a solution that requires a particular sort order in the base sheet. 

This is the formula used to try to pull that data:

=INDEX({Deal Name}, MATCH(MAX(COLLECT({First Date}, {Contact Name}, [Contact Name]@row)), {First Date}, 0))

 

Could anyone help??

 

Thank you!

Paul Newcome

Could it be that multiple deals were submitted by the same person on the same day? Using the MAX function is looking for a single MAX. If there are multiple entries for the same person on the same day, it could be skewing things.

jschumacher

Hi Paul,

Yes! That looks like what's causing the issue. Any ideas on what to do? To be clear, it's not that there are multiple deals from the same Contact Name on the same day, it's that if Deal_A comes in on 1/1/2018 from Contact_1 and Deal_B comes in from Contact_2 on the same day, "Most Recent Deal" for both Contact_1 and Contact_2 are Deal_A (when it should be Deal_A for Contact_1 and Deal_B for Contact_2).

Paul Newcome

It sounds like there may be an issue with the Contact Column(s). Are either or both of them set to allow multiple contacts per cell?

jschumacher

Nope 

It's not a Contact List column, just a text/number column. (Technically we list by Bank and not by actual contact name)

Paul Newcome

Are you able to provide some screenshots?

eric.o

Hello,

 

I agree, a screenshot would be best to further investigate this situation. As a quick check, you may want to confirm the spelling is correct for the desired max cell in relation to the cell being source. It may be there is a character off causing it to not be recognized as an option for the MAX. 

 

I would also suggest connecting with us via our technical support forum that way we can set up a screen share to assist in resolving the issue. https://help.smartsheet.com/contact

 

Have a wonderful day,

Eric  -  Smartsheet Technical Support

jschumacher

I've attached some screenshots--I hope they make sense. I needed to sanitize the data since it's confidential.

I've also reached out to Tech Support per Eric's recommendation. (Note that we use "Deal Name" and "Opportunity Name" somewhat interchangeably. In this thread I've mostly referred to "Deal" but we use "Opportunity" in the actual sheet.

Thank you again for your time!

Paul Newcome

Ah. Ok. Part of the problem is that you are pulling the date in your MATCH function. Granted you are specifying to pull the max date for that client, but the result of that is still going to be a date that your MATCH function is looking for and will return the row number from the first occurrence of that date.

Basically you are not specifying a unique identifier. One option for that would be to use a helper column on the first sheet to create a unique ID. You could even use a JOIN function for say the bank name and date (or any other way you can think of to create a unique ID).

Duplicate that column on the summary/tracker sheet and use that in your MATCH function.

jschumacher

Very excited to try this! But you lost me at "Duplicate that column on the summary/tracker and use that in your MATCH"

I understood the first part to mean: add a "Deal ID" column to the "Deal Tracker" base sheet (the one where all of this data originates). AutoNumber would be fine by me. But I'm confused about where to go from there.

Thank you for your patience and help!

Paul Newcome

I was thinking using a column where you would use something along the lines of

=[Bank Name Column]@row + " " + [Date Column]@row

to return a result of 

"Bank Name mm/dd/yyyy"

.

If you have a Deal ID column with unique ID's for each deal, you would still need to find a way to return that unique ID into the MATCH portion of your formula.

If you use the same helper column as above on both sheets, it will provide a unique ID to match against that is easily replicated because you are joining the same data.

So if Bank A and Bank B both have a deal on 06/21/2018, they can be easily identified by the helper columns on both sheets as 

Bank A 06/21/2018

and

Bank B 06/21/2018

.

You can automate the date cell by using a MAX(COLLECT(..........)) function looking at the date column and comparing to the bank name.

With the date automatically pulling the latest date and combining it with the bank name then using that to look for the data in the same row as the matching "bank name / date" on the master sheet, all you should have to do in your summary is enter the bank name.

Basically you would

1. Enter Bank Name

2. Automation checks master sheet for most recent date associate with that bank name and pulls it

3. Automation combines the bank name and latest date to form a specific identifier (also automatically done on the master sheet)

4. Automation looks for that specific identifier in the master sheet

5. Automation pulls the data from the column in the row established in step 4

6. You have now established reliable criteria (bank name, date, deal name) for further automated data pulls

.

Are we getting closer to a solution?

 

jschumacher

Yes!! I think I get it now. It appears to be working. Thank you so so very much!!

Paul Newcome

Excellent. Happy to help.

 

If you run into any issues while testing or in use, feel free to mention it here, and we'll see if we can give it a tweak.

Jen Nguyen

Strange, I posted a question yesterday and it disappeared.

Genevieve P.

Hi @Jen Nguyen

I split your comment from this post and created a new question from it, for more visibility:

https://community.smartsheet.com/discussion/comment/239744#Comment_239744

Jen Nguyen

Oh thank you! Please delete my comment from this morning then.