2

I've got one sheet, named External_Data, that contains data that is pulled via a connection. A second sheet references these data with a simple index-match formula, but is returning errors for some reason and I'm losing my mind trying to figure it out. When I point the formula to a different sheet with similar data that is also pulled via a connection, it works just fine. I'm able to go to External_Data and see the value, I can do a quick search for the value and it pops up fine, but the formula just will not work.

The formula in question is:

=INDEX(External_Data!J:J,MATCH(B2,External_Data!A:A,0))

Column B, a list of serial numbers, is the formula:

=IFERROR(Calcs!G:G,"")

and External_Data column A is just a list of serial numbers (imported via connection as mentioned above)

Things I've tried:

  • Changing the contents of column B to be just the value rather than the formula

  • Changing the lookup value to VALUE(B3)

  • Changing the lookup value to the actual value

  • Rebuilding the entirety of External_Data from scratch

  • Changing the formula to a vlookup (=VLOOKUP(B2,External_Data!A2:J750,10))

I have no idea what's going on with this crap. It SHOULD work. It works elsewhere. It works when I point the formula anywhere else. But for some reason it refuses to cooperate with this particular sheet.

  • 1
    Top of my head, my guess would be that the number being pulled in from the external source is a number stored as text? Some programs that export data to older versions of Excel do this, such as Hyperion exporting to Excel 2.1 (an issue I had in the past) – DavePenn Sep 7 '17 at 20:43
  • @DavePenn alright, went into the source and you were correct - everything was stored as text. changing it to a number corrected the issue. Is there any way to resolve this issue without having to reformat the source data? That file will be overwritten daily and reformatting it is an extra step I'd like to avoid. Would adding a helper column that just VALUEs be the simplest method? – Tock Sep 7 '17 at 20:59
  • 1
    Yes, import the data and create a separate helper sheet with the formula =VALUE for all the cells and reference the helper sheet in your formulas. Otherwise, you could write a macro that would open the source file and reformat the cells to number upon workbook open – DavePenn Sep 7 '17 at 21:02
2

Instead of changing the format of the source, what about changing the format of your lookup value for the purpose of the MATCH? You say you tried VALUE(B3) but it was already a number, and the problem was being compared to the strings in External_Data.

How about

...MATCH(TEXT(B2,"0"),External_Data!A:A,0))

So that you're comparing apples to apples (or more specifically, strings to strings) for your index-match without having to alter the data source or set up a helper sheet?

|improve this answer|||||
  • This is a better answer than my comment, nice one! – DavePenn Sep 9 '17 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.