I need help with a formula

Dakota

I am working on my {main sheet} and referencing a sheet named {tracking}

I am trying to return a value of a cell if the "dates" match and the "names" match in my {main sheet} and my {tracking sheet}, it should return a value from the {tracking sheet}.

Does anyone understand this?

Thank you,

D

Genevieve P.

Hi @Dakota

It looks like this is missing a range... I can't quite tell what your ranges are based on the name, but this would be the structure of an INDEX(COLLECT:

=INDEX(COLLECT({Value To Return}, {Column 1}, [Value 1]@row, {Column 2}, [Value 2]@row), 1)

So in your instance, we're either missing the column range that goes along with matching [Hidden Team:]@row, or we're missing the column to pull back information from.

=INDEX(COLLECT({Value To Return}, {Team Column}, [Hidden Team:]@row, {Date Column}, [Form Due Date:]@row), 1)

Does that help?

Genevieve

Alejandro Ortega

Hey Dakota, i do understand your problem, i believe i can help you, can you provide maybe a screen capture of both of your sheets?

Thanks

Dakota

Top is {Main Sheet}

Bottom is {Tracking} You can see tracking sheet has #unparseable. Where the form due date is 02/04/2021 - I need the return value to = 10 - because on both sheets the team and the dates match.

Genevieve P.

Hi @Dakota

You'll want to use an INDEX(COLLECT formula to compare the different values and return a cell if all your values match.

Take a look at Paul's solution on this other post for an example: https://community.smartsheet.com/discussion/69141/can-you-use-2-match-criterias-in-an-index-match-formula

Let me know if you still need help!

Cheers,

Genevieve

Dakota

Thanks @Genevieve P. Iv been playing with that function plus others... I seem to always get back errors unperishable, incorrect functions, or incorrect arguments.

I must not be inputting this correctly....

=INDEX(COLLECT({Hours and Miles Form - Oil Change Sheet Range 3}, [Hidden Team:]@row, {Hours and Miles Form - Oil Change Sheet Range 5}, [Form Due Date:]@row), 1)

Genevieve P.

Hi @Dakota

It looks like this is missing a range... I can't quite tell what your ranges are based on the name, but this would be the structure of an INDEX(COLLECT:

=INDEX(COLLECT({Value To Return}, {Column 1}, [Value 1]@row, {Column 2}, [Value 2]@row), 1)

So in your instance, we're either missing the column range that goes along with matching [Hidden Team:]@row, or we're missing the column to pull back information from.

=INDEX(COLLECT({Value To Return}, {Team Column}, [Hidden Team:]@row, {Date Column}, [Form Due Date:]@row), 1)

Does that help?

Genevieve

Dakota

=INDEX(COLLECT({Value To Return}, {Column 1}, [Value 1]@row, {Column 2}, [Value 2]@row), 1)

This I understand !!!

Thank you!!

Genevieve P.

Haha no problem at all! Glad I could help.