=LOOKUP not working correctly

jb@59069

Hello,

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

LOOKUP Table.JPG

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

LOOKUP Problem.JPG

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

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

Genevieve P.

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.

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.