INDEX Formula Not pulling data

ErinHell

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)), "")

Lucas Rayala

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

ErinHell

@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.

Paul Newcome

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)?

ErinHell

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)))

Paul Newcome

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?

ErinHell

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!

Lucas Rayala

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.

ErinHell

@Lucas Rayala Thanks that formula helped now my PM won't have to manually input that on every order.