VLookup returning values from a different row

I'm stumped on this one. I'm running the following formula:

=VLOOKUP("In Progress", [Phase - Task]:[Phase Status], 1)

1st column in the table is Phase - Task. VLookup finds the value "In Progress" on row 6 in the Phase status column, but then returns the value from Phase - Task from row 4. I've never seen VLookup pull data from a different row than where it found the search value.

Can anyone spot a reason why this is happening, and a potential fix? Thank you!

Find more posts tagged with

Formulas

Accepted answers

Leibel S

You can use an INDEDX MATCH combo.

This would let you put columns wherever you want.

=INDEX([Phase - Task]:[Phase - Task],MATCH("In Progress",[Phase Status]:[Phase Status],0))

All comments

Leibel S

@Alex Folck

Not sure what you are trying to accomplish, but the way your formula is written the only possible value it can return is "In Progress".

It is looking for "In Progress" in the [Phase - Task] column and returning the value from that row in the [Phase - Task] column (which would automatically be "In Progress")

https://help.smartsheet.com/function/vlookup?frame=0&nav=1

Alex Folck

Oh, do I just need to reorder the range? I put the range in order that the columns appear on the sheet. When I move the Phase Status column to the left of the Phase - Task column, it returns properly. Still don't know what would cause it to return a value on a different row

Alex Folck

https://community.smartsheet.com/discussion/comment/350753#Comment_350753

reordering the range didn't work sadly. What I'm trying to accomplish is have the Phase - Task name appear in my sheet summary when it is "In Progress" at the Phase Status column

Leibel S

To accomplish this:

Your columns should first be status then task.
Your formula should be: =VLOOKUP("In Progress", [Phase Status]:[Phase - Task], 2)

Alex Folck

It's a bummer that they have to be in that order, I'm assuming to keep the order that exists in my current design, I'd have to do something more complicated?

Leibel S

You can use an INDEDX MATCH combo.

This would let you put columns wherever you want.

=INDEX([Phase - Task]:[Phase - Task],MATCH("In Progress",[Phase Status]:[Phase Status],0))

Alex Folck

OK I'll give that a shot, thank you! Can you help me understand what that index - match combo would look like if I wanted to return multiple values when more than one line shows In Progress?

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.