INDEX Formula Not pulling data
I have a master sheet that I am using an INDEX formula to pull data in from other sheets based on a common data element. I am using this on different columns in the master sheet and of the 6 only 3 are working. I know there is a match so the 3 not working should be pulling in. Here are the formulas I am using. Any ideas why they might not be finding the match?
Columns where the formal isn't working: Percent Complete, Project Phase, and Go Live Projection.
Formula for Percent Complete: =IFERROR(INDEX({Intake Sheet Percent Complete}, MATCH([Order Number]@row, {Intake Sheet Order Number}, 0)), "")
Formula for Project Phase: =IFERROR(INDEX({Intake Sheet Status}, MATCH([Order Number]@row, {Intake Sheet Order Number}, 0)), "")
Formula for Go Live Projection: =IFERROR(INDEX({Intake Sheet Go Live}, MATCH([Order Number]@row, {Intake Sheet Order Number}, 0)), "")
Answers
-
Hi @ErinHell, to confirm:
- you are using the same "MATCH" with other formulas, and it is working with those formulas?
- you have double-checked that the cross sheet references that your INDEX is using as a target range is actually selecting a column? sometimes SS hiccups at just the wrong moment and you have to re-select.
Are the non-working columns just blank or are you getting an error? What's the error?
A couple of things to try:
- copy one of the working formulas, delete the (working) target range and re-add the correct target range
- if it still doesn't work, remove the "IFERROR" to see what appears in the cells that you would expect to see a value
-
@Lucas Rayala I removed my IF error and it says No Match. But when I look at the sheet I see the match is there.
-
Are you able to provide some screenshots of both sheets? Exactly how is your Order Number being populated and with what type of data (on both sheets)?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Properties on both fields are text/number.
The order number is a hyperlink that we manually input and as I am typing this I am realizing that is probably my issue!
=(INDEX({Intake Sheet Percent Complete}, MATCH([Order Number]@row, {Intake Sheet Order Number}, 0)))
-
But if it is working on some, it should be working on all. You said you are pulling data from other sheetS. Are the formulas that are working pulling from a different sheet other than the sheet being referenced in your above non-working formulas?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
No, they were referencing other fields. I made a duplicate column and typed the values in and updated my formula and it worked! So the hyperlink was my issue. Thank you for your help!
-
EDITED -- saw you got it figured out after I answered, but if you use my formula you won't have to type them manually. Great job sleuthing the issue!
Hi @ErinHell -- did you get it figured out? I'm guessing you were correct regarding the hyperlink. I did a few tests and the issue is specifically with numbers that are hyperlinked (isn't a problem with hyperlinked words). Best solution is to create a helper column next to your column with hyperlinked text. In that column, add this column formula (I'm assuming "Order Number" is your column name with hyperlinked values):
=IFERROR(VALUE([Order Number]@row),[Order Number]@row)
The "VALUE" function converts your hyperlinked number to a regular number that can be referenced. The IFERROR is there to protect against an error. VALUE functions error out if they see a letter, and that can break cross-sheet references. If the value function errors out, the IFERROR will return the contents of the cell, but not as a value.
Anyway, point your formula at the new helper column instead of the column with hyperlinks. Should work.
-
@Lucas Rayala Thanks that formula helped now my PM won't have to manually input that on every order.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!