INDEX (COLLECT) Formula returning #INVALID VALUE Error
I am trying to use INDEX COLLECT to pull data out of a specific column from another sheet based one three criteria.
My formula is:
=INDEX(COLLECT({Site Visit Intake  DOM Review %}, {Site Intake FDCFW}, FW@row, {Site Visit Intake  DC}, DC@row, {Site Visit Intake  Metric}, [Metric Being Evaluated]@row),1)
I keep getting a #INVALID VALUE error despite ensuring that my source sheet has matching information.
Source Sheet:
Note that FW is feed from a formula, "DC" "Metric Being Evaluated" are dropdowns, and "DOM Review %" is a formula as well.
From Formula: Site Visit Intake  DOM Review % = "DOM Review %), Site Intake FDC  FW = "FW", Site Visit Intake  DC = "DC", and Site Intake  Metric = "Metric Being Evaluated:
Landing Sheet:
Any help would be greatly appreciated!
Answers

What are the formulas for FW and %?

Hello Paul,
The FW column is a formula to turn a date into a fiscal week and year. (NOTE FY starts 4 weeks into calendar year)
=IF(ISBLANK(Date@row),"",(YEAR(Date@row)+""+IF((WEEKNUMBER(Date@row)  4)<=0, WEEKNUMBER(Date@row)+48,WEEKNUMBER(Date@row4)))
The % Formula is just a formula to pull the info from whichever column it is in. The form is designed to ask a series of questions based on 5 different metrics with each metric having its own comment field. The form will only be filled out for one metric. So the column just pulls it to that column as an easy point to reference from
=IF(ISBLANK([Metric Being Evaluated]@row), "", IF([Metric Being Evaluated]@row = "Metric 1", [M1 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 2", [M2 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 3", [M3 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 4", [M4 Comments]@row, IF([Metric Being Evaluated]@row = "Metric 5", [M5 Comments]@row))))))

As an update, I made a helper columns where I input the information manually to test if the formula and drop down source columns were the issue. The formula worked then.
As the source sheet is meant to be fed from the form and then referenced into another sheet via INDEX COLLECT, is there a way to get around the formulas and dropdowns through additional functions in the formula.

I think I see where one potential issue may be...
How is the FW column populated in the Landing Sheet (the one containing the INDEX formula)?

It is populated as a text/number field. The landing page is an Insight Engine that is fed from a Metric Shuttle through automation.
The info that is fed into the metric shuttle is populated from a Tableau report which is exported into a in an excel format and copied into the metric shuttle sheet.

What happens when you double click into one of the FW cells in the Landing Sheet? Is there a leading apostrophe there that is hidden from view when you are not editing the cell?

No apostrophe

Ok. So your current formula:
=IF(ISBLANK(Date@row),"",(YEAR(Date@row)+""+IF((WEEKNUMBER(Date@row)  4)<=0, WEEKNUMBER(Date@row)+48,WEEKNUMBER(Date@row4)))
is populating a text string that just looks like numbers, but the data coming into the Landing sheet is numeric data. Even though they look similar, you are comparing numbers to letters looking for a match.
You can convert the string output by the formula into a number using a VALUE function as below which should then mean you are comparing numbers to numbers and should get your match.
=IF(ISBLANK(Date@row),"",VALUE(YEAR(Date@row)+""+IF(WEEKNUMBER(Date@row)  4<=0, WEEKNUMBER(Date@row)+48,WEEKNUMBER(Date@row4)))

The Positive is that the adding the VALUE to the FW formula from the Source page worked. Thank you very much for that.
Current issue is that the formula is not working consistently for every instance and I am trying to troubleshoot that issue. Below is a snapshot of my Source sheet. Note that rows with Yellow highlights signify that the landing page has an instance where the info should be pulled (Matches in the FW, DC, and Metric Being Evaluated Columns). The Green highlights signify that the landing page actually pulled the info and showed a result.
SOURCE PAGE:
LANDING PAGE
The first row is pulling from Row 3 of the Source sheet. Row 2 should be pulling from Row 2 of the Source sheet but it is not.
(NOTE that I wrapped the INDEX COLLECT formula in an IFERROR formula to return the  if no value is returned from the formula. The results in term of pulling was exactly the same before adding the IFERROR)
Thanks!

What do you get in row 2 of the Landing Page when you remove the IFERROR?

I get a #INVALID VALUE

Can you show the row from the source sheet that the Landing Page should be matching on and pulling from?

Please reference the highlighted row.

Ok. We have a few troubleshooting steps to work through. I would suggest a temporary helper column in the Landing Page for this troubleshooting.
Lets start with this (on row 2):
=COUNTIFS({Site Intake FDCFW}, FW@row, {Site Visit Intake  DC}, DC@row, {Site Visit Intake  Metric}, [Metric Being Evaluated]@row)

I received a #INVAILD REF error. The first screen shot I added to show the formula into the Helper Column
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!