Help with IF(INDEX(MATCH

2»

Answers

  • @Paul Newcome yes sir, only one cell in the Task Name column would contain "First Customer Event", the problem is there needs to be additional qualifying language in the cell after that prefix to identify what that event actually was and it's going to differ from sheet to sheet.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =INDEX(COLLECT({BNYCompletion}, {BNYTask}, 1, {BNYTaskName}, CONTAINS("First Customer Event", @cell)), 1)


    Try that with your "First Customer Event" modification and let me know how it goes.

  • Zach Johnson
    Zach Johnson ✭✭
    edited 05/29/20

    @Paul Newcome thanks! Sorry for additional questions but what is the @cell piece of the formula above? Is that an individual cell link

    That cell is not going to be in the same row on each sheet, it's going to vary across the board so i can't reference a single cell and keep that formula consistent across the entire data set.


    Also, don't i need to include a MATCH to return the corresponding cell in column Start Date?

  • @Paul Newcome I'm sure I'm making this more confusing than it needs to be and I appreciate your patience with me.

    Here are the filters I need the formula to go through in order to return a date from a column "Start Date":

    1 - find row in column "Task Name" that contains "First Customer Event"

    2 - Is the corresponding cell in column "Completion" = 1 (aka 100%)?

    3 - If yes, then Match (or return to my formula sheet) the date in corresponding cell in column "Start Date"

    4 - If no, leave blank

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @cell stays as is. Basically it tells the formula to evaluate the previously specified range on a cell by cell basis. For example...

    =COUNTIFS(Status:Status, @cell = "Complete")

    This tells the COUNTIFS to count how many cells in the Status column are "Complete".


    Here is a breakdown of how the formula works...

    =INDEX(COLLECT({BNYCompletion}, {BNYTask}, 1, {BNYTaskName}, CONTAINS("First Customer Event", @cell)), 1)

    We use a COLLECT function to pull together all cells in the first range that match the following range/criteria sets.

    If only one cell within the Task Name range is going to CONTAIN "First Customer Event", then the COLLECT will only pull one cell.

    The COLLECT function cannot operate as a standalone, so we use the INDEX function which pulls data.


    In typing this up, I realized I actually made a mistake in the formula. You want the Start Date. I saw "Completion" and got a little ahead of myself.

    Here it is corrected.

    =INDEX(COLLECT({BNYStart}, {BNYTask}, 1, {BNYTaskName}, CONTAINS("First Customer Event", @cell)), 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    And just saw you most recent comment... Here is some tweaking...

    =IF(INDEX(COLLECT({BNYCompletion}, {BNYTask}, 1, {BNYTaskName}, CONTAINS("First Customer Event", @cell)), 1) = 1, INDEX(COLLECT({BNYStart}, {BNYTask}, 1, {BNYTaskName}, CONTAINS("First Customer Event", @cell)), 1))

  • Zach Johnson
    Zach Johnson ✭✭
    edited 05/29/20

    Thanks @Paul Newcome !

    I'm getting INVALID, may be because BNYTask and BNYTaskName are the same column. Here's a screen shot that may help.

    Wasn't sure which column you were referencing when you used BNYTask and BNYTaskName.

    Columns needed to reference in formula are TaskName, Completion, Start

    Also, the cell that now reads "Customer Pitch" in column "Task Name" will be where I change it to "First Customer Event - Customer Pitch"

    I owe you a beer :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. The screenshot with the column names definitely helps. Try this one...

    =IF(INDEX(COLLECT({BNYCompletion}, {BNYTaskName}, CONTAINS("First Customer Event", @cell)), 1) = 1, INDEX(COLLECT({BNYStart}, {BNYTaskName}, CONTAINS("First Customer Event", @cell)), 1))


    NOTE: Sorry about missing the first screenshot.

  • It worked! My man @Paul Newcome! 🍻

    Last question until next week - is there a quicker way to link individual cells rather than using the cell linking button for individual cells.

    If I'm inputting data onto one sheet but want that to be separate from where i'm doing formulas on that data for quality control purposes, is there a way to get that data into the formula sheet without having to individually link each cell.

    Intent is to automate the transfer of the data within the cells from the input sheet to the formula sheet.

  • @Paul Newcome, hope you have been well. Next issue I'm facing -

    I've created a series of sheets who's purpose is to provide a "Top 10" list for certain metrics. So my source sheet contains a column who's contents are all whole numbers (many instances of the same number). The formula I've written to return the 10 largest numbers in the column, and then corresponding customer name is as follows:

    =IFERROR(LARGE(COLLECT({TimeinContracting}, {Status}, {Status} = "Active"), 1), "-")

    =IFERROR(LARGE(COLLECT({TimeinContracting}, {Status}, {Status} = "Active"), 2), "-")

    =IFERROR(LARGE(COLLECT({TimeinContracting}, {Status}, {Status} = "Active"), 3), "-")

    =IFERROR(LARGE(COLLECT({TimeinContracting}, {Status}, {Status} = "Active"), 4), "-")

    =IFERROR(LARGE(COLLECT({TimeinContracting}, {Status}, {Status} = "Active"), 5), "-")

    so on until...

    =IFERROR(LARGE(COLLECT({TimeinContracting}, {Status}, {Status} = "Active"), 10), "-")


    The problem is that when there is more than one customer with the same number in column {TimeinContracting}, my formula that returns the associated customer name just returns the first customer name in the column that matches that number - so in some cases I have the same customer listed more than once because my formula found that customer name first and returned it a second, third, fourth, fifth time even though there were 4 other customer names associated with the same number in column {TimeinContracting}.

    Here's the formula i'm using to match customer name to the number that the formulas above return:

    =IFERROR(INDEX({Customer}, MATCH([Time in Contracting]1, {TimeinContracting}, 0)), "-")

    Screenshots:

    So the first formulas I referenced above are in column Time in Contracting and the second formula is in column Customer.

    I need to find a way to return the actual associated customer name from the source sheet, rather than just the first customer name that matches the value in Time in Contracting on the source sheet because I will have the same number for many of these metrics. All of these are going into reports I've built.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Here is my suggestion...

    Change your LARGE formula to something along the lines of...

    =IFERROR(LARGE(DISTINCT(COLLECT({TimeinContracting}, {Status}, {Status} = "Active")), #), "-")


    Then replace your INDEX/MATCH with something along the lines of

    =JOIN(COLLECT({Customer}, {TimeinContracting}, [Time in Contracting]@row), ", ")


    Here is how that should work:

    First screenshot is the sample data. Second screenshot is the output of the above formulas.

    Source Data:


    Output:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!