IF AND INDEX MATCH to pull the correct row based on criteria

Michaela Kamenska

Hello all,

I am struggling to come up with the correct formula to pull the right information between master sheet and personal tracker and I wondered if you can help. My logic for creating the formulas is not the very best.

What I'm essentially trying to do is pull the right start/end date and budgeted hours from the master tracker. Because there are multiple rows with the same project code, a simple index/match just won't do. Can you please advise how to pull from this?

I created a hidden column in the personal tracker with the name of the assignee and basically, if the row in the master tracker has SOW of certain value and the name matches, then pull the date from that.

The same goes for #of budgeted hours (personal tracker) vs MO hours (master sheet).

Please see below for the layout of personal tracker (pic #1) and master tracker (pic #2).

The master tracker is composed of parent and children rows as seen in the second picture (not sure if that is important to mention).

Paul Newcome

Try an INDEX/COLLECT instead. Something like...

=INDEX(COLLECT({Master Sheet Date Column}, {Master Sheet Name Column}, [MM sheet name]@row, {Master Sheet SOW Column}, SOW@row), 1)

Paul Newcome

Try an INDEX/COLLECT instead. Something like...

=INDEX(COLLECT({Master Sheet Date Column}, {Master Sheet Name Column}, [MM sheet name]@row, {Master Sheet SOW Column}, SOW@row), 1)

Michaela Kamenska

Hi Paul,

Because the same person is assigned to multiple projects with the same SOW, I somehow need to incorporate the Study # into it to index against that. Is that possible?

Also, based on the formula you provided I'm getting #INVALID VALUE even though all columns are date columns.

Paul Newcome

To incorporate the Study #, we would continue with the range/criteria pattern within the COLLECT function.

=INDEX(COLLECT({Master Sheet Date Column}, {Master Sheet Name Column}, [MM sheet name]@row, {Master Sheet SOW Column}, SOW@row, {Master Sheet Study # Column}, [Study #]@row), 1)

As for the error... Are you able to copy/paste the exact formula you have directly from your sheet to here?

Michaela Kamenska

Hi Paul,

This is the edit:

=INDEX(COLLECT({Start Date}, {Staff}, [MM sheet name]@row, {Master_SOW}, SOW@row, {Master_Study #}, [Study #]@row), 1)

I adjusted the reference names for easiness. I'm now getting #INCORRECT ARGUMENT SET

Could it be because the MM Sheet name is set as ="surname, first name" and then causing havoc? It is essentially the same value and I changed the properties of the column to be the exact same as the source sheet. Same goes for SOW.

Paul Newcome

The incorrect argument set error is indicating that your ranges are most likely not the same size/shape. Are all of the cross sheet references pointing to the same sheet? Are you clicking on the column header to select the entire column or just selecting a specific range of cells?

Michaela Kamenska

Hi Paul,

Just went to double check and they are all referring to the same source sheet and the whole columns are highlighted. I'm not sure anymore how to fix this?

Paul Newcome

Ok. Try this...

Remove the formula from the sheet entirely.

Log out.

Clear cookies and cache.

Log in.

Manually retype formula.

Michaela Kamenska

@Paul Newcome I tried and it doesn't want to work. When I change the 1 at the end to 0 though, it gives me #Date Expected error. Is there any other formula we could try?

Paul Newcome

Ok. That is both a step in the right direction and the wrong direction at the same time...

The Date Expected error means that it is pulling data, but it is not a date. How is the [Start Date] column in your source sheet setup? Is it a date type column? How is it populated? Manual entry or do you pick a date from the calendar?

Changing the 1 to a 0 should actually break the formula though. Here's why...

=INDEX({range to pull from}, row_number)

The {range to pull from} in this particular instance is the COLLECT function. Having the zero on the end is basically telling the formula to pull from row zero. There is no such thing as "row zero" which should throw an error.

Michaela Kamenska

Hi Paul,

I don't know what happened, but it's working fine today, thank you! :)

Paul Newcome

Glad it is working now. Phew.

Happy to help. 👍️