Index/Match Help

Since Excel created XLOOKUP, I have lost my ability to properly do index/Match, I am getting an #INVALID COLUMN VALUE error. First here is my formula:
=INDEX({Deliverable Tracker Approved Archive_Delivery Liaison download complete}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0))
This screenshot is from my file named Deliverables tracker. It is also where the Index/Match formula is.
These are just temporary columns while I capture the data. My other sheet where I am getting my data from is called Deliverable Tracker Approved Archive. On that sheet, this is the unique key and then the data I am trying to capture:
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
Best Answer
-
Is the image below what you want?
In that case, use a Text/Number column, [Upload Status] for example, and modify the formula like this;
[Upload Status]=IF(INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0)), "Upload Complete", "")
Answers
-
Hi @SherryFox
Perferpse your Help4 column type is wrong; it is not a checkbox, but a Text/Number of something.
In your formula below, the INDEX function references a Checkbox column, which returns a True or False value.
=INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0))
However, if your Help4 column's type is text/number, you assign the column the wrong type values, true/false. Thus, you get the "#INVALID COLUMN VALUE" error.
For example, in the [Help 4 - Text/Number + ""] column, I added '''' to your formula.
=INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0)) + ""
As the image below shows, it returns true/false values.
Please check if the column type matches the type of column you are referencing with the INDEX formula.
Sample data sheet
(This published sheet is editable, so you can check how the formula works by checking / un-checking)
-
No that is not working right. I need to pull the values from the checkbox column (Liaison download complete) from the Deliverable Tracker Approved Archive into the result for the Deliverable Tracker. There have been some changes on the second sheet, so I would love a TRUE (box is checked) to equal "Upload Complete"
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
-
Is the image below what you want?
In that case, use a Text/Number column, [Upload Status] for example, and modify the formula like this;
[Upload Status]=IF(INDEX({Deliverable Tracker Approved Archive_Liaison downl}, MATCH(Help@row, {Deliverable Tracker Approved Archive_Help2}, 0)), "Upload Complete", "")
-
That is not working; apparently my initial helper columns are not unique, so no matches are being created
Sherry Fox
Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024
Core App Certified 🦊
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!