Inconsistent Results of the Same Formula in Different Cells

Here's the situation:

I've got two sheets in a workbook, one is a sort of rudimentary map of a work site that shows the serial numbers of components in cells that represent where on the site a component is located. The other sheet is a table that compiles all sorts of different data about each component (most important to the map is the serial number and a location).

I created the following lookup formula to code the map to show the serial number of each component in its location on the map:

Formula:

Please Login or Register  to view this content.

This formula works exactly as it's meant to. The correct serial number shows up when I update that position on my table (Component_Logistics) and the color-coded conditional formatting changes appropriately to show that the space is occupied when a serial number is present.

Now... There are two other positions, "Rack: 2" and "Rack: 3" which are directly above the aforementioned cell, one after the other. When I put in the same formula, except with "Rack: 2" and "Rack: 3" instead of "Rack: 1", what I end up seeing is the formula written out in those cells instead. I tried changing the formula back to "Rack: 1" to see if that made a difference, and it didn't. The formulas still show up. I cleared the conditional formatting rules, to no avail, and even tried copying the formatting from the correctly working cell--also to no avail. There is no discernable difference in format between either of the three cells, but for some reason only one of them will work properly and for the life of me I can't figure out why!

I've attached two images. One is the 'wrong format' and the other is how it's meant to look. Both images rely on the same formulas, the only difference is the locations at the beginning of the formulas.

Wrong Format.png
Wrong Format

Right Format.png
Right Format

If you see the formula rather than the result of the formula, the chances are the cell(s) are formatted as text.

Format as General and re-enter the formulae.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

You're welcome. Thanks for the rep.