Formula help please

Hi

I am trying the formula below and it is returning a #NO MATCH (see image below for the Factory QC Signoff Ass n Fin reference sheet)

=VLOOKUP([Clean Job Number]@row, {Factory QC Signoff Ass n Fin}, 5, false)


Perhaps the issue is that the "Clean Job Number" column has a formula to extra just the 5 digits of our job number?

=LEFT([Job Number]@row, 5)


Any help will be gratefully received


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 07/05/22 Answer ✓

    Hi @Tracey Tume

    Yes, it's possible that in one sheet your Job Number is seen as numerical values but in your current sheet with the VLOOKUP formula, the "clean" number is read as a text string, so it's not finding a match.

    Try wrapping a VALUE function around your current "clean" formula, like so:

    =VALUE(LEFT([Job Number]@row, 5))

    This should change it to be read as a number. Let me know if this resolves the issue!


    As a side-note, I would personally recommend using INDEX(MATCH instead of using VLOOKUP. This is because an INDEX(MATCH has two separate ranges for your two columns, and doesn't require you to select a range across unnecessary data (e.g. "Description", "Name" columns).

    Because the two columns are selected individually, this means you could change the organization of your source sheet, moving columns around, and it won't break any formulas. It also pairs down the number of cells being referenced which will reduce complexity and help the sheet run faster.

    Here's the structure of an INDEX(MATCH:

    =INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

    So in your case:

    =INDEX({Finishers Name Column}, MATCH([Clean Job Number]@row, {Job Number Column}, 0))

    See: Formula combinations for cross sheet references


    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 07/05/22 Answer ✓

    Hi @Tracey Tume

    Yes, it's possible that in one sheet your Job Number is seen as numerical values but in your current sheet with the VLOOKUP formula, the "clean" number is read as a text string, so it's not finding a match.

    Try wrapping a VALUE function around your current "clean" formula, like so:

    =VALUE(LEFT([Job Number]@row, 5))

    This should change it to be read as a number. Let me know if this resolves the issue!


    As a side-note, I would personally recommend using INDEX(MATCH instead of using VLOOKUP. This is because an INDEX(MATCH has two separate ranges for your two columns, and doesn't require you to select a range across unnecessary data (e.g. "Description", "Name" columns).

    Because the two columns are selected individually, this means you could change the organization of your source sheet, moving columns around, and it won't break any formulas. It also pairs down the number of cells being referenced which will reduce complexity and help the sheet run faster.

    Here's the structure of an INDEX(MATCH:

    =INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

    So in your case:

    =INDEX({Finishers Name Column}, MATCH([Clean Job Number]@row, {Job Number Column}, 0))

    See: Formula combinations for cross sheet references


    Cheers,

    Genevieve

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    Hi Genevieve

    I must be a nerd and I was so excited when this worked! ha ha

    I love learning new formulas so both of those suggestions are ones I will use again.

    Many thanks.

    Tracey

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Tracey Tume

    We're all nerds, here! 🤓 Glad it worked for you.

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!