Level 1 (16 points)

Numbers VLOOKUP formula not working with usernames ending in ~ symbol

Has something changed with the text that is acceptable with VLOOKUP formulas in Numbers? I do a spreadsheet that lists the number of hours each user name logs in for work. It shows the daily counts, a new day on each tab, and then a summary tab that counts the total for the month using VLOOKUPS. I have been doing this for years so I have archived spreadsheets where the formulas worked. In the last couple of weeks, the VLOOKUP formula stopped populating the entire column. And if I manually copy it down, it just returns zeroes for the whole column, but those results are false. After a LOT of trial and error, I found this only happens when the username contains a ~ symbol at the end of the text. So if the username is JohnDoe~ it will not work, but JohnDoe will, and so will ~JohnDoe, or John~Doe. It only seems to matter if it's at the end. As soon as I take that out, the VLOOKUP works perfectly. I can go into past spreadsheets where the formula is working, and as soon as I recalculate those formulas, they stop working. I'm not changing anything, just clicking on the cell and pressing the enter key to recalculate. It used to work at the time I made those old spreadsheets, but now it doesn't. Is this a glitch? A new rule with a recent update? We have a lot of usernames with that symbol so it would be a huge pain to change that because this data is downloaded and it would be more work to manipulate the data to make the formulas work.

Posted on Oct 10, 2026 4:33 PM

5 replies

Camelot

Level 9 (62,211 points)

Oct 11, 2026 10:27 AM in response to eitak0702

I can reproduce this, and assume that the ~ is affecting the lookup since it's a key character in regular expressions (used to match a single character).

There's a simple fix, though.. edit your function to:

=VLOOKUP(T(A),Table 1::A:G,7)

Wrapping the lookup value (A) within the T function forces Numbers to recognize the values as plain text and not to honor regular expression tags.

What's even weirder (and I can't explain this one), is if you enter the formula using T(A), then edit the formula to remove the T() function, it continues to work, even with the ~ in the cell values. However, entering the formula as just (A) fails... curiouser and curiouser.

eitak0702 Author

Level 1 (16 points)

Oct 11, 2026 11:03 AM in response to Camelot

Thanks Camelot. I also found a workaround using the SUBSTITUTE function with VLOOKUP (example below). I'm sure there are more ways to get around this specific issue. My bigger concern is why this happened. It was working up until maybe 3 weeks ago, so it's like something changed with the capabilities of the app and I'm curious to know what else would be affected so I can fix it now before it becomes a problem. I really don't want to go through and fix all my formulas, only for this to happen again later for another reason. I'd never be able to trust the data, which is the whole purpose of this report. Numbers allows you to use the ~ character, just not at the end of the text, which is just bizarre. Like, either block it completely or not at all, but partially? Makes no sense.

VLOOKUP(SUBSTITUTE(A,"~", "~~",occurrence),'TEST RAW DATA PAGE'::A:G,7,FALSE)

Camelot

Level 9 (62,211 points)

Oct 12, 2026 2:22 PM in response to eitak0702

I can't tell when/why it changed, and I don't have any previous examples or versions of Numbers to test it on.

However, the latest version, 15.4, landed about 2 weeks ago, and that would seem to correlate to when the change triggered on your side. There's nothing in the release notes about text-based changes, only about new functions added, but it's not unreasonable to think that it was previously classified as a bug that's been fixed (Apple don't generally call out every bug fixed in new versions), or there was a general update to the text parsing functions that's triggered this. Hard to know for sure.

Either way, I would still advocate for the T() function to normalize the text value - it should work for a wide range of cases, whereas your SUBSTITUTE() will only catch ~ and ignore any other potential characters.

Yellowbox

Level 7 (22,545 points)

Oct 11, 2026 4:55 AM in response to eitak0702

Hi eitak0702,

An interesting problem! Please show us the VLOOKUP formula.

More information often leads to a solution.

Regards,

Ian.

eitak0702 Author

Level 1 (16 points)

Oct 11, 2026 10:48 AM in response to Yellowbox

Hi Yellowbox,

The VLOOKUP is in the screenshots provided.

Originally posted at discussions.apple.com/thread/256373936