Lookup result one row off

Question

I am using the lookup vector function. Suddenly, my result is always one row off. It is returning B3 for A4. Has anyone experienced this? How can I fix it?

Answer 1

Sure - it's happening no matter how basic I get: =lookup(J2, E:E, G:G). (It started happening in a more complicated scenario, but now I can't get it to work correctly anywhere)

Hi,

I would expect =lookup(J2, E:E, G:G) to work in the normal way and find the value of J2 in EE and return the corresponding value in G:G but there are circumstances where it might not.

Lets say that J2 was a calculated value containing several decimal places of which we're displaying 2. The way lookup works is that it searches for an exact match but if it doesn't find it then it returns the largest match that is less than the lookup value and because the real value isn't what we see in j2 this coulld mean it returns results we don't expect.

You may have to ROUND the lookup value in J2

Answer 2

Hi, try this formula

=index(G:G,match(J2,E:E,0))

Answer 3

It is returning B3 for A4.

That indicates a row off AND a column off...

Answer 4

Sure - it's happening no matter how basic I get: =lookup(J2, E:E, G:G). (It started happening in a more complicated scenario, but now I can't get it to work correctly anywhere)

Answer 5

Hi, can you post the formula you are using