Help with IF(INDEX(MATCH

Zach Johnson

@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

=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

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

Zach Johnson

@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

@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

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

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

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.

Zach Johnson

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

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.

Zach Johnson

@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

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:

Haley Gehris

@Paul Newcome I wrote the following formula and it worked in the first row, however when I made it a column formula the following rows are blank. Any ideas?

=IF(INDEX({KFS data Range 2}, MATCH([PO #]@row, {KFS data Range 3}, 0)) = 6021, INDEX({KFS data Range 1}, MATCH([PO #]@row, {KFS data Range 3}, 0)))

Paul Newcome

@Haley Gehris Are you able to provide some screenshots for context?