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?


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @andihawes08,

    could you please supply screen shot for both sheet.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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?

  • 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.



    Associate Director

    Copernicus Consulting Pte. Ltd.

    P: +65 9230 5657 | E: [email protected]

    Feel free to reach out for licenses, services, and training on Smartsheet

  • 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.
    Genevieve P. Employee Admin

    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]


    {Cross sheet Column Reference}

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!