Hi all,

First question in the forum. I'm in need of help with an INDEX(MATCH( formula that I've been using but now need to make conditional on another column. Essentially, I only want the INDEX(MATCH( formula to return a value when a column "Completion" is changed to "100%". I've tried nesting the existing INDEX(MATCH formula into an IF statement but I'm getting an INVALID OPERATION message.

Original formula =INDEX({LibertyStart}, MATCH(PA$3, {LibertyMilestones}, 0))

Attempt to make it conditional =IF({LibertyCompletion}="100%",INDEX({LibertyStart}, MATCH(PA$3, {LibertyTask}, 0)),0)

The intent is to return a start date from column "Start" for a specific task, defined by PA$3, from column "Task" when the column "Completion" is changed to "100%".

I'm in a data collection sheet, referencing a source sheet.

I would greatly appreciate any assistance/suggestions!

Best Answer


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You would need to use an INDEX/MATCH to pull the % Complete value as well.

    =INDEX({LibertyCompletion}, MATCH(PA$3, {LibertyMilestones}, 0))

    Then you would say that IF the above is equal to one, run the original INDEX/MATCH

    =IF(INDEX({LibertyCompletion}, MATCH(PA$3, {LibertyMilestones}, 0)) = 1, INDEX({LibertyStart}, MATCH(PA$3, {LibertyMilestones}, 0)))

  • I'm doing something similar but have a condition where I the If statement as the first condition, the second condition is the specialist assignment based on the director and project manager.

    The Index Match works fine I just need to add the first condition with an IF statement. See my formula below.

    Project Type = First condition A&E and assigns the specialist name who will be the only person working on those contracts. Second condition assigns the other specialists (multiple) based on a referenced worksheet based on the assigned project manager and director.

    Any ideas? Any other way to accomplish this?

    IF([Project Type]A&E, "[email protected]", [INDEX({Operations_ContractsMatrix Range 4}, MATCH([Director Role]@row, {Operations_ContractsMatrix Range 3}, 0))])

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Kathy Beers I'm not sure I follow. Are you able to provide more details as to how everything is working together and exactly what you are wanting to accomplish?

    Screenshots would also be very helpful. Sensitive/confidential data can be removed, blocked, and/or replaced with "dummy data" as needed.

  • Another idea if you have multiple criteria and don't want to nest too many If statements is to create a "helper column" combining the two data sources you are looking for so your Index/Match can be done based off of the one field. You can hide the helper column if you like.

  • edited 05/21/20

    @Paul Newcome ,

    Thank you so much!

    This worked beautifully:

    =IF(INDEX({BNYCompletion}, MATCH(FCE$4, {BNYTask}, 0)) = 1, INDEX({BNYStart}, MATCH(FCE$4, {BNYTask}, 0)))

    My next question is how could I incorporate an OR statement(s) in order to have the referenced sheet searched for multiple values rather than just matching for FCE$4. Meaning depending on the nature of the sheet it's looking at, FCE$4 could vary (only 3 option in total). Trying to write the above so that it would match either FCE$4, FCE$3, or FCE$2. Whichever was present in the target sheet.

    Ya'll are awesome, thanks for the help!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You would need to write separate INDEX/MATCH statements for each on and then nest those inside of the OR statement.

    =IF(OR(INDEX({BNYCompletion}, MATCH(FCE$4, {BNYTask}, 0)) = 1, INDEX({BNYCompletion}, MATCH(FCE$3, {BNYTask}, 0)) = 1, INDEX({BNYCompletion}, MATCH(FCE$2, {BNYTask}, 0)) = 1), INDEX({BNYStart}, MATCH(FCE$4, {BNYTask}, 0)))

  • edited 05/21/20

    Thank you, @Paul Newcome! I would also then need to Index BNYStart and Match that back to BNYTask for either FCE$4, FCE$3, FCE$2. I'm getting NO MATCH with the above formula, but the target sheet contains the value in FCE$3.

    Would I need to add this component - INDEX({BNYStart}, MATCH(FCE$4, {BNYTask} - for each of the three options (FCE$4, FCE$3, FCE$2)?

    It looks like the formula above is only indexing BNYStart and matching back to BNY Task for FCE$4.

    If so, how/where should I add it in?

    Thank you sir!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ah. Understood. My apologies for mixing that up. We would actually use a nested IF statement instead of the OR.

    =IF(INDEX({BNYCompletion}, MATCH(FCE$4, {BNYTask}, 0)) = 1, INDEX({BNYStart}, MATCH(FCE$4, {BNYTask}, 0)), =IF(INDEX({BNYCompletion}, MATCH(FCE$3, {BNYTask}, 0)) = 1, INDEX({BNYStart}, MATCH(FCE$3, {BNYTask}, 0)), =IF(INDEX({BNYCompletion}, MATCH(FCE$2, {BNYTask}, 0)) = 1, INDEX({BNYStart}, MATCH(FCE$2, {BNYTask}, 0)))))

  • Ahh, yes I see. Learning so much! Really appreciate you, @Paul Newcome. Will let you know how it goes shortly.

    Thank you, sir!

  • edited 05/21/20

    @Paul Newcome I'm getting #NO MATCH still. The references are correct and the sheet in reference contains the values i'm trying to index and match. Not sure where I'm going wrong.

    Referenced sheet: See row with "Present Solution Overview Presentation to Customer"

    Formula in collector sheet:

    =IF(INDEX({Completion1}, MATCH("Present Solution Overview Presentation to Customer", {Task1}, 0)) = 1, INDEX({Start1}, MATCH("Present Solution Overview Presentation to Customer", {Task1}, 0)) = IF(INDEX({Completion1}, MATCH("Language2", {Task1}, 0)) = 1, INDEX({Start1}, MATCH("Language2", {Task1}, 0)) = IF(INDEX({Completion1}, MATCH("Language3", {Task1}, 0)) = 1, INDEX({Start1}, MATCH("Language 3", {Task1}, 0)))))

    Intent is to return the start date of a given row that matches one of the three options when the percentage column is changed to 1 (100%).

  • @Paul Newcome that worked! Thanks so much.

    One more question for you, what if i wanted to Match to a cell that contained "First Customer Event" followed by some differing language in each sheet rather than Match to some text in FCE$4 for example.

    Essentially, the First Customer Event could come in several different forms, but I need to consolidate start dates across multiple sheets for this "event". So I'm hoping to change the formula above to maybe use an IF CONTAINS to look for "First Customer Event" in a cell that would then be followed by some arbitrary language that would not be standard across sheets.

    Hopefully that makes some sense. Just looking for a way to only have the Index Match look for "First Customer Event" in column BNYTask although that cell will also contain some add'l arbitrary language and then return the corresponding date in column BNYStart.

    Thank you once again, Sir!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I'm not sure I follow...

  • edited 05/29/20

    Sorry, @Paul Newcome

    So in the previous version we were matching to some text that was in FCE$2, FCE$3, or FCE$4. Instead of having to write that formula which contains 4 sheet references for each FCE option (alot of manual referencing), I can add a prefix to the FCE options within each smartsheet i'm pulling data from for example.

    FCE2 = Present Pitch Deck Presentation to Customer

    FCE3 = Present Solution Overview to Customer

    FCE4 = Customer Kick-Off Meeting

    Depending on the nature of the sheet i'm referencing, one of the three options above will be a task in a cell within the Task Name column - (BNYTask) in our formulas above.

    What I'm trying to do now is instead of having to write that long formula with 12 references for each sheet for 100+ sheets, I can retro-actively add a prefix "First Customer Event" to each of these task rows in the sheets i'm looking at so they would now within the sheets i'm referencing for start dates the task names would become:

    First Customer Event - Present Pitch Deck Presentation to Customer

    First Customer Event - Present Solution Overview to Customer

    First Customer Event - Customer Kick-Off Meeting

    Then, i'm hoping we can write a formula that will first Index the completion column to make sure it's 100%, then Match that to the task that beings with "First Customer Event", and then Indexes the Start Date column and Matches it to the same Task row that contains "First Customer Event". I just need to return the corresponding start date into my formula collection sheet.

    I was thinking we might could use some combination of IF, CONTAINS, or HAS, just something that adds that condition where the task name row must contain "First Customer Event" (even though there will be a string of text following it) instead of the three different options I was using.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Would only one of them have the "First Customer Event" prefix at a time?

  • @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 NewcomePaul 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.

  • 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 NewcomePaul 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 NewcomePaul 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))

  • 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 NewcomePaul 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 NewcomePaul Newcome ✭✭✭✭✭

    You can link cells in bulk so long as the selection sizes are an exact match. So if you select a 3x4 grid on your target sheet and then select the option to link cells from another sheet, you can select a 3x4 grid on your target sheet and link all of those at once.

    But those grid sizes MUST match. You can't select a 3x4 grid and then try to link cells that are in a 4x3 grid.

Sign In or Register to comment.