Excel VLOOKUP failure (#N/A) where equal test indicates a match

Question

I'm currently getting

=VLOOKUP(D5,'Per payment&sub'!D$8:'Per payment&sub'!D$394,1,FALSE)

returning #N/A, where I was expecting a match. Have done the requisite googling, but all suggestions for this situation that I can find indicate that if the above doesn't work, it should be because the equals test fails for the cell it's expected to match - i.e. in our case that

=D5='Per payment&sub'!D12

Should return FALSE (and then the troubleshooting goes on from there to work out exactly how the values expected to match are different - data type, trailing space etc).

The thing is, in our case that test returns TRUE. So excel thinks those two values match when asked directly, but when asked if D5 matches any value in a range that includes 'Per payment&sub'!D12 ('Per payment&sub'!D$8:'Per payment&sub'!D$394 as per the above VLOOKUP) it wants to tell me no.

Anyone have any idea how that circle can be squared? Have tried a few different things:

• Replacing the original formula-calculated values in the range being looked up with static values
• isnumber() tests on both cells (both return true)
• type() tests on both cells (both return 1)
• having the sheet reference only at the start of the range - i.e.

=VLOOKUP(D5,'Per payment&sub'!D$8:D$394,1,FALSE)

• using an equivalent MATCH function instead of VLOOKUP
• narrowing the range (for both VLOOKUP and MATCH) to the single cell it's expected to match
• removing the '&' from the sheet name in case that was throwing things off (the sheet containing the VLOOKUP is called 'Bank transfers', which should be a safe enough name, I think...)
• referencing and then copying the static values from the other sheet ('Per payment&sub') to the sheet the formula is on)
• Checking all involved cells for leading apostrophes (although presumably if either the search key cell OR the match range cells had them, and the other didn't, the =cell1=cell2 test would have failed)
• Wrapping the reference to the search key cell in VALUE(), and also TEXT()

But everything seems to give the same results - the cells are the same when compared directly, but forget about getting VLOOKUP or MATCH to behave.

Anyone have any ideas how we could be getting success on the individual comparison, but failure on the match within a range containing the individual cell? TIA

Update: Have tried copy pasting the data to a fresh workbook (as per Muji's suggestion below that settings specific to the file may be at play - the file did originate from a different organization so that is not impossible), with both the values to search for and the range to search in in the same worksheet:

Screenshot of formulas:

Screenshot of values

Issue persists. Copy pasting from A6 to E5 in the above does result in the formulas in N5 and O5 (those that use E5 rather than -C5 for the search key) returning the expected result (which indicates that the formulas are correct, but of course otherwise defeats the purpose). Copying A6 to C5 and removing the minus sign from the formulas in K5 and L5 likewise.

Update 2: Copied again to a new file (and actually saved it this time), uploaded if anyone feels like looking at the data itself.

Also excel version is office 365, Version 1908 Build 11929.20300 Click-to-Run, if that's likely to be relevant at all.

Also - after fixing the formulas in the file as uploaded (in column G - nothing that affects the outcome, I just missed the $s in the Range section - but since the corresponding value in the range is always on the same row as the search key cell or below the range being searched always still has the matching value in it), I'm now noticing an excel warning:

A value is not available to the formula or function.

Not able to find anything on google that indicates why this might be the case though, and completely puzzled why it would affect some rows and not others.

Update (final): Still don't know what caused the issue, but copy-pasting from excel into word and then back appears to have fixed it, so it would seem there was some kind of metadata with those values in the original spreadsheet that was stuffing things up. While the answer below didn't include anything that solved the problem directly, it was correct to suggest that this might be the problem and as such has been accepted.

Answer 1

Well, since we do not have the actual data, we can only make guesses as this obviously turns on some tiny technicality.

The data is from an outside source. The data in the first pic is at the left edge of the screen. Usually that implies it is treated as text. I DO notice it has had some formatting in the second pic, but that first column could be simply right justified rather than literally made into numbers from a text start.

So I will suggest that is the problem. Some of the data in the lookup column are text. Not just formatted as text, but preceded by the ' forcing many of Excel's operations and functions to treat them as text. For instance, the table being looked up in (A1:A394) creates an array of values ({-3.5;252.05;...}) that the lookup is actually performed in.

So far, so good. But if an entry in Column A is '-3.5 vs. -3.5 then its entry in that array is "-3.5" NOT -3.5 and the lookup will fail giving you the #N/A error (so: {"-3.5";252.05;...} ).

This kind of thing is a COMMON problem with outside data ("outside" to you and the things you directly control). One would expect all the values to be so preceded, but I guess only if a machine produced them. If there was human hand entry along the way, well, game on. And if the data are collected from several sources, one or more of which might do this even if not all do, game on too.

If the problem existed in Column C, it wouwldn't actually be a problem, exactly, as the lookup value is not resolved into an array, but just as the thing you literally see and it now has a - before it so Excel treats it as a number. The table (A1:A394) does NOT get this treatment as it resolves into the array form and text items get double quoted. In the "does it equal the cell I expect it to locate" situation BOTH items are single items and not returned inside the calculation in array form so the double quoting does not occur in those tests which is why their success vs. the lookup's failure.

Presumably one is hand entering the lookup value (D5 in the original) and so it will be a number no matter what as far as the lookup is concerned. Yet even if the lookup value gets the preceding single quote, the formula treats it as a number as described. So... Key element is it is a single cell so does not get returned in array form inside the calculation of the formula.

This is a situation easy to test. Look at the failures to see if a text marker is present, usually the single quote. (Some people and some programs might still sometimes produce those entries.) Or just format a cell as General, enter a 1, and Paste|Special|Multiply the failed value, or just the whole column at once. In my testing, this does work nicely, like it usually does.

Had to test though, as this sometimes fails to force the change. But it works so all is good with trying that.

The only thing of half a dozen I tried that let the failure occur is this. May not be the solution for your situation, but give it a whirl as the underlying idea is a VERY common difficulty in outside data.

There's a lot of particularioty here. If I formatted both the Column A and C cells as text and used the preceding single quote, the only way I could get the formula to work was to wrap the cell with VALUE() so it would treat it as a number and combine it as desired with the -, and then wrap all that in TEXT() to turn the sign-changed value back to text to match the text in Column A. So order and particularity matter here if things are a mix of good data and offending data.

Again though, without the actual spreadsheet for someone to examine closely for these technicalities, no one can do more than guess.