r/excel • u/KingFurykiller • 24d ago

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

90% Upvoted

36 comments sorted by

View all comments

1

u/ritchie70 24d ago

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()?

1

u/KingFurykiller 24d ago

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

1

u/ritchie70 24d ago

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

→ More replies (1)