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