VLOOKUP Not Working With Primary Column Indented Values

I have a nested IF statement with a VLOOKUP that seems to have stopped working. For debugging, I've stripped out all but the VLOOKUP function and made the search value static, "DSHS".

Formula: =VLOOKUP("DSHS", [Agency & Administration]23:[Agency-Div Sort Order]24, 22, false)
"Agency & Administration" is the Primary Column and column 1 in the lookup table.
"Agency-Admin Sort Order" is column 22 in the lookup table. (23 total columns in the table.)

With the lookup table restricted to a single row, 23, the formula works with a result of "10000". When I expand the lookup table to include row 24, it returns "#INVALID OPERATION".

When I change the search value to "ALTSA" and the lookup table rows to 25-33 it correctly returns "11100". When I expand the lookup table by 1 row to 25-34, it's "#INVALID OPERATION".

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

What exactly are you wanting to accomplish?

Pat Canning

I'll lead with, I'm not looking for a different way to do it. I can think of at least one other method that might work. I posted because it appears there's a bug in VLOOKUP right now, as described in the post. I did find in this community forum that someone else had this problem in ~2021-2022.

But the specific answer is… This sheet supports multiple reports. One report uses summary data in the 'top half' of the sheet. Another report uses data in the 'bottom half' of the report. The "Agency & Administration" column values are repeated, but not necessarily 1:1 between 'top half' and 'bottom half'. Rows 23 thru 114 have sort orders assigned to them in columns 22 & 23. Beginning in row 116, I want to populate the already-assigned (from the 'top half') sort orders when the "Agency & Administration" values match.

As mentioned in the OP, it works when there's no indentation of the values in the "Agency & Administration" column. It sometimes works when there are only two levels of indentation but not always, as described in the OP if you follow along with my narrative.

Paul Newcome

If it is a bug and you are not looking for another way to accomplish this, then you may want to reach out to support instead.

https://community.smartsheet.com/categories/get-help

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.