Losing hair from index:match

dwfender

Hey All. I've read through every post I can find on index match but my formula is still coming up unparsable no matter what I try.

I am trying to reference columns across 2 sheets. I've created references in the end sheet under Manage References. Here's the latest version of my formula and then a list of things I've tried that still don't work.

These two are on a separate sheet called 360PAS where I created the reference with the following names:

Column Name in Manage Reference for match: [TASK_NAME] - The item being referenced

Column Name for lookup: [TASK_END] - the return data (this is a date column in date format)

in the sheet where I'm trying to get the value to populate....

=index([TASK_END],MATCH([Primary Column]@row,[TASK_NAME],0))

Other solutions i've tried include

=index([TASK_END],MATCH("Opening Date",[TASK_NAME],0))

=index([TASK_END]:[TASK_END],MATCH([Primary Column]@row,[TASK_NAME]:[TASK_NAME],0))

and along with that, adding iferror to every combo of the items above.

I CAN get index match to work if I draft the formula using data in the same sheet that I'm writing the function, but cross sheet reference kills it every time.

Any help is greatly appreciated!

Bassam Khalil

Hi @dwfender,

Could you please provide a screenshot of your two sheets after removing any sensitive information? This will help me craft the correct formula tailored to your needs.

Paul McGuinness

Hi @dwfender

Your formula looks sound as does your explanation and the references, only thing i would check is your column on the source sheet where you are entering the formula set up as a date column? as trying to index match a date into a non date column will return that error.

Hope that helps

Paul

dwfender

Hey @Bassam Khalil

Working on it now and here's what I have.

The date column formatting is set to DATE.

The task names on the left are linked cell references so I know the naming conventions stay up to date.

Report where I want to generate the index(match)

Source Date

Managed References

Bassam Khalil

Hi@dwfe@dwfender

Ok I will check it and come back to you soon

Bassam Khalil

Hi @dwfender,

please try the following formula in the report sheet and convert it to column format formula:

=IFERROR(INDEX(COLLECT({TASK_END}, {TASK}, [Primary Column]@row), 1), "") 

here is a screenshot shows the result:

the source sheet

the report sheet ( the formula )