Can VLOOKUP Column_num return an answer from a colukn which is auto numbered?

Ive got a VLOOKUp to another sheet to work when the lookup_table is a single column, but when i extend the lookup_table to 6 columns, and the 'column-num' is 1, so left column of range, the formual doesnt work. It returns 'NO Match' when i know there is a match?

If i do a lookup_table range of just the column i need to find the search_value in, and enter 1 for column-num, then the formula works?

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @andihawes08,

could you please supply screen shot for both sheet.

andihawes08

2nd SS shows formula to first SS. Formula is looking at Document Full Name, trying to find it on Range 3 on first sheet, which is first 6 columns from Task Id to Full Name, then reporting value in column 1, which is Task ID. No Match.

In SS below, if i edit Range to be single column of 'Full Name', it works and it does find a match, then the reporting value of 1 reports back the Full Name Value.

Why does it not find my value with a larger range?

Aravind GP

Hi,

In VLOOKUP, the look up value should be in the left of the column from where you intend to pick corresponding values. It doesn't work when the look up value resides in a column to the right of the column that you intend to pick the values from.

A good alternative will be using Index and Match. I had provided some details on Index & Match in another comment. Link given here. If you still have questions, feel free to reach out to me.

https://community.smartsheet.com/discussion/comment/269047#Comment_269047

andihawes08

Thank you,

i have got INDEX/MATCH to work on 2 sheets. I also need it to work on a single sheet but it doesnt appear to work the same way, as i cant select a Range of Columns like i do when the Index/Match Range Selection when it looks a secodn sheet?

Genevieve P.

Hi @andihawes08

If you're building a formula within the same sheet, you'll need to reference the column with different syntax:

[Column Name]:[Column Name]

Versus

{Cross sheet Column Reference}

You can read more about referencing columns in our Help Center, here!

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.