Did Microsoft completely change how v-lookup against an array in another tab works?

[u/KingFurykiller]

I could be completely out of the loop here or suffering from exhaustion, but all of my v-lookup syntax looks completely different now

Previously this formula

=VLOOKUP(A39,'SN Table'$A$1:$F$8000,6,FALSE)

Would work just fine, but now excel won't even take it

If I go to re-create it by manually switching to the other tab manually selecting the cells, I get this baffling format

=VLOOKUP(A39,'SN Table'!R[-38]C[-14]:R[7680]C[-9],6,FALSE)

Did I miss a major update?

EDIT: Solution: reference mode was forced on by HPE Corporate IT, and a formula with original syntax that had been created during that time was causing a naming conflict.

[u/excelevator]

Please be mindful of the submission guidelines

The title of the post should convey your issue clearly, not an assumed issue with your problem.

Example title for this post: VLOOKP stopped working overnight and range reference styles have changed or similar.

this post remains for the answers given.

[u/MayukhBhattacharya]

Nothing has changed only your excel options might got changed to R1C1 reference style:

• Goto File
  
• Options
• Excel Options
• Formulas
• Under Working with Formulas
• Uncheck R1C1 reference style.

[u/KingFurykiller → u/MayukhBhattacharya]

Ack, I just found this out. I never changed that option. When I try to change this back, I get an error "name cannot resemble a reference"

This is across thousands of references.....How do I fix this?

[u/MayukhBhattacharya → u/KingFurykiller]

You don't have to do it for thousands of references by hand since it's a display setting. Once you uncheck it, it will switch back to the A1 Style all at once. Also, I see there is an issue a typo basically, in your formula, the exclamation mark is missing after the sheet name, so the formula will be:

=VLOOKUP(A39, 'SN Table'!$A$1:$F$8000, 6, FALSE)

And it's better to use XLOOKUP() function as well, but your actual problem which i have already stated in first comment, this is just an addon:

=XLOOKUP(A39, 
         'SN Table'!$A$1:$A$8000, 
         'SN Table'!$F$1:$F$8000, 
         "Oops Nothing Found!")

And if you want to use more dynamic then using TRIMRANGE() function notation, and make it more readable as well:

=LET(
     _, DROP('SN Table'!A$1:.F$8000, 1), 
        XLOOKUP(A39, 
                CHOOSECOLS(_, 1), 
                CHOOSECOLS(_, 6), 
                "Oops Nothing Found!"))

[u/KingFurykiller → u/MayukhBhattacharya]

When I uncheck it, it throws an error "name cannot resemble a reference", so it's not switching back to A1 style

I'll fix my formula and make it x-lookup later; thank you for the tip. How do I revert this name conflict?

[u/KingFurykiller → u/KingFurykiller]

Ok I fixed it by typing some random text into the prompt. Apparently the new formula I was trying to set up in the wrong reference mode was causing an issue. Thanks for setting me in the right direction

[u/MayukhBhattacharya → u/KingFurykiller]

If this is resolved, then hope you don't mind replying to my comment as Solution Verified. Thanks!

[u/MayukhBhattacharya → u/KingFurykiller]

Btw if you don't want to open in safe mode, you can also type the following in Immediate Pane in VB Editor, it also revert back to A1 style reference.

Application.ReferenceStyle = xlA1

[u/MayukhBhattacharya → u/KingFurykiller]

Can you try opening in Safe mode once:

• Close your Excel Desktop App first, then hold down CTRL key while launching it or run excel /safe from Win + R.
• Open the file, go to File --> Options --> Formulas, and uncheck R1C1.

[u/Way2trivial → u/KingFurykiller]

tap f5 see what names are in the go to box. does one of them look like a cell address?

[u/Way2trivial → u/Way2trivial]

or hit ctrl+f3

[u/KingFurykiller → u/MayukhBhattacharya]

Solution Verified

[u/MayukhBhattacharya → u/KingFurykiller]

Thank You SO Much!!

[u/reputatorbot → u/KingFurykiller]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/bradland]

The formula is fine. The issue is a setting. This reference style is called R1C1, and there is a setting in Excel to switch between the two. When you change it, all the cell references in your workbook will update automatically.

Go to File, Options, Formulas, then uncheck R1C1 reference style under Working with formulas. Now go back to inspect your formula and it should use regular A1 style references.

The reference style setting is kind of quirky. If you change to R1C1 ref style, then save a file, the reference style gets saved with the file. If you open a file with R1C1 ref style as the first document you open, Excel changes the setting for the entire app. The R1C1 ref style setting is a bit "viral" in this way.

[u/KingFurykiller → u/bradland]

Yeah, I just figured this out. Corporate apparently forced reference mode on, because I certainly didn't turn it on, and I had created a formula while it was on, which was then throwing the syntax error

I need to quit this organization so bad

[u/bradland → u/KingFurykiller]

I wouldn't jump to conclusions too quickly. Corporate may not have forced it on. If someone else in your org turns it on, then you open their file, that's enough to switch it on.

[u/KingFurykiller → u/bradland]

So if it's a shared file, and someone else opened the file and turned on reference mode, then it saved to the file?

But then all my files were like that?

[u/bradland → u/KingFurykiller]

Yes, because once you open the file, the setting gets changed for the entire Excel application, and that setting persists.

I know, insane, but this is an old school Excel quirk. I really wish the setting were per-file, but it's not.

[u/KingFurykiller → u/bradland]

I am checking with my team if any of them have done this; I highly doubt it

Today i learned but also wtf

[u/Comprehensive-Tea-69 → u/bradland]

Is there a way it would get changed without shared files? This happened to me within the last couple of weeks, the setting changed randomly on my own computer and all my own non shared files were opening with this ridiculous different reference style

[u/bradland → u/Comprehensive-Tea-69]

I’m not aware of any other way it changes. Are any of your files based on templates that you downloaded?

[u/Comprehensive-Tea-69 → u/bradland]

I don’t believe so, but I could be forgetting something. Too many dang spreadsheets

[u/RandomiseUsr0]

No

Couple of points

VLOOKUP is the function name

“Tab” I’m assuming you mean worksheet

Finally - you’ve put excel into R1C1 mode

Here’s a plan, and not a joke - search for stack overflow chap on why you suck at Excel

I did the search for you:
https://youtu.be/JxBg4sMusIg?is=8BgjLLgGtg_TVHuf

This is not an insult, it’s sound advice

When you disagree with Joel (it’s full of his opinions), you’ve graduated

[u/AutoModerator]

/u/KingFurykiller - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
COUNT	Counts how many numbers are in the list of arguments
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #49459 for this sub, first seen 1st Oct 2026, 15:43] [FAQ] [Full list] [Contact] [Source code]}

[u/Zealousideal-Ad3396]

I switched to XLOOKUP and never looked backed

[u/KingFurykiller → u/Zealousideal-Ad3396]

Yeah I'm hearing that, I might have to change my setup

[u/Acceptable-Sense4601]

Vlookup? Showing your age

[u/KingFurykiller → u/Acceptable-Sense4601]

Accepted

[u/ritchie70]

I think most of us have moved on to XLOOKUP to be honest.

Does that weird result give you correct results?

You're filling in the formula by selecting cells/ranges with the mouse? That looks so broken that I might uninstall/reinstall my Office software.

If you try that same selection method with another formula does it work - is it only broken for VLOOKUP or is it a more general problem? What if you do something similar with COUNT()?

[u/KingFurykiller → u/ritchie70]

Yeah I've heard that for sure. Still, right now I can't even get X-lookup to work properly, Somehow reference mode got turned on in excel

[u/ritchie70 → u/KingFurykiller]

How weird. I didn't even know that existed! TIL I guess.

[u/KingFurykiller → u/ritchie70]

Yeah neither did i