=LOOKUP not working correctly

Options
jb@59069
jb@59069 ✭✭✭✭✭✭

Hello,

I have multiple sheets that need to use this exact =LOOKUP function, but I cannot get it to work correctly.

See LOOKUP Table.jpg for the data, TC1 = left column, TC2 = right column, rows 17-22

See LOOKUP Problem.jpg for what gets displayed: TC Class = Left column, TC Class $ = right column

The formula in TC Class $: =LOOKUP([TC Class]@row, $[TC1]$17:$[TC2]$22, 2)

Why do "#NO MATCH" and incorrect values show up?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @jb@59069

    There's an optional setting at the end of a VLOOKUP function to specify if you're looking for an exact match or approximate match. I notice you haven't specified this in your formula, so it will default to approximate. Try adding "false" to the end of your formula to ask it to find an exact match, like so:

     =VLOOKUP([TC Class]@row, $[TC1]$17:$[TC2]$22, 2, false)

    See here for more information: VLOOKUP Function


    If this hasn't resolved the issue, I'd be interested to see if an INDEX(MATCH formula would find the match, instead.

    Try this:

    =INDEX($[TC2]$17:$[TC2]$22, MATCH([TC Class]@row, $[TC1]$17:$[TC1]$22, 0))


    Let me know if either of these have worked for you!

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @jb@59069

    Thanks for pointing that out! I see that it's referenced in the syntax but not in the formula example, I'll pass your feedback along.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!