Help with IF(INDEX(MATCH

Zach Johnson

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!

Paul Newcome

My apologies. When writing up the formula I accidentally threw in extra = signs. That's what I get for copy/pasting. Try this.

=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)))))

Paul 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.

Paul 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)))

Kathy Beers

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, "jane.doe@company.com", [INDEX({Operations_ContractsMatrix Range 4}, MATCH([Director Role]@row, {Operations_ContractsMatrix Range 3}, 0))])

Paul 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.

Zachary Hall

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.

Zach Johnson

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

Zach Johnson

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

Zach Johnson

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

Thank you, sir!

Zach Johnson

@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

My apologies. When writing up the formula I accidentally threw in extra = signs. That's what I get for copy/pasting. Try this.

=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)))))

Zach Johnson

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

I'm not sure I follow...

Zach Johnson

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 Newcome

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