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)?
-
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?
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!