I am trying to return a value based on a couple of criteria. I'm using the index/collect method.

snowmic1

I have 2 sheets. Sheet 1 has a list of names. Sheet 2 has a list of locations, dates, and who is there across 6 columns. My goal is to have sheet 1 list where these people are currently located.

Sheet 1:

Sheet 2:

Formula for "Current Site" field on Sheet 1:

=index(collect({Sheet 2-Column1},{Sheet 2-Column30},"Yes",{Sheet 2-Tech 1-6},[Lead Tech]@row), 1)

Where Column 30 on Sheet 2 will say "Yes" when Today() falls within the date range. Thus if today was 6/29, Joe Smith would be at Location 2 with Phil Billson.

Genevieve P.

HI @snowmic1

You're correct, the COLLECT range won't be able to look into all 6 Tech columns in one range to search all of those cells for one name and then check your other columns - it would need each individual column to be referenced and checked separately.

What I would do in this instance is actually add a helper column to your main sheet with all the techs listed. If you add a Multi-Select column, you can use a JOIN formula to bring all the tech names into one cell as multiple, individual values. (You can then hide this column as it's not needed on that sheet).

=JOIN([Tech 1]@row:[Tech 6]@row, CHAR(10))

The CHAR(10) separates the names into multi-select values.

Then in your collect formula you can check to see if that one column, the multi-select column has the specific Lead Tech name (along with other names). This way you're still only referencing one column but you have all the values from that row to look at!

=INDEX(COLLECT({Sheet 2-Column1}, {Sheet 2-Column30},"Yes", {Sheet 2- Multi Select}, HAS(@cell, [Lead Tech]@row)), 1)

Let me know if this makes sense and will work for you.

Cheers,

Genevieve

snowmic1

I can get it to work if I only reference one column when looking at [Lead Tech]@row. But then it's just coincidence of the name happens to be in that column and has a "Yes."

=index(collect({Sheet 2-Column1},{Sheet 2-Column30},"Yes",{Sheet 2-Tech 1},[Lead Tech]@row), 1)

This works but only coincidentally. It doesn't look at the whole row for the name. So it'll never tell me Joe Smith is at Location 2.

Genevieve P.

HI @snowmic1

You're correct, the COLLECT range won't be able to look into all 6 Tech columns in one range to search all of those cells for one name and then check your other columns - it would need each individual column to be referenced and checked separately.

What I would do in this instance is actually add a helper column to your main sheet with all the techs listed. If you add a Multi-Select column, you can use a JOIN formula to bring all the tech names into one cell as multiple, individual values. (You can then hide this column as it's not needed on that sheet).

=JOIN([Tech 1]@row:[Tech 6]@row, CHAR(10))

The CHAR(10) separates the names into multi-select values.

Then in your collect formula you can check to see if that one column, the multi-select column has the specific Lead Tech name (along with other names). This way you're still only referencing one column but you have all the values from that row to look at!

=INDEX(COLLECT({Sheet 2-Column1}, {Sheet 2-Column30},"Yes", {Sheet 2- Multi Select}, HAS(@cell, [Lead Tech]@row)), 1)

Let me know if this makes sense and will work for you.

Cheers,

Genevieve