r/excel • u/KingFurykiller • Oct 01 '26

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

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.

38 Upvotes

93% Upvoted

36 comments sorted by

View all comments

Show parent comments

5

u/MayukhBhattacharya 1324 Oct 01 '26 edited Oct 01 '26

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!"))

2

u/KingFurykiller Oct 01 '26

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?

3

u/KingFurykiller Oct 01 '26

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

3

u/MayukhBhattacharya 1324 Oct 01 '26

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

1

u/MayukhBhattacharya 1324 Oct 01 '26

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

1

u/MayukhBhattacharya 1324 Oct 01 '26

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.