ABS and vlookup wrong result

Hi,

I have this formula:
=(VLOOKUP(A6,datas!C:E,3,FALSE)-(VLOOKUP(A6,datas!C:Q,15,FALSE))/ABS(VLOOKUP(A6,datas!C:Q,15,FALSE)))
The result, formatted as percentage is : 1100%

First vlookup returns :12 ; Second and third (same vlookup) return :33

If i split the formula like below, the result is different:

If i have the first vlookup in E6, second one in F6 and third one in G6, i will get this:
12...33...33
=(E6-F6)/ABS(G6)...the result will be -64%

Can you please tell me why i don't get the same result with the big "ABS vlookup" formula?

Thank you kindly,

Puiuluipui

You put an extra set of Parens in the formula using vlookups..
=(VLOOKUP(A6,datas!C:E,3,FALSE)-(VLOOKUP(A6,datas!C:Q,15,FALSE))/ABS(VLOOKUP(A6,datas!C:Q,15,FALSE)))

using your example with cell references, that would be translated like
=(E6-(F6)/ABS(G6))


Try
=(VLOOKUP(A6,datas!C:E,3,FALSE)-VLOOKUP(A6,datas!C:Q,15,FALSE))/ABS(VLOOKUP(A6,datas!C:Q,15,FALSE))

It's working with a new pair of brackets.

Thank you very much,

Puiuluipui

Thanks!!

Puiuluipui

Glad to help

But I'm curious

Originally Posted by puiuluipui

It's working with a new pair of brackets.

What do you mean a "New" pair of brackets?
My suggestion was to "Remove" a pair of brackets that didn't belong...

Hi Jonmo,

this is my first attempt:
=(VLOOKUP(A6,datas!C:P,3,FALSE)-(VLOOKUP(A6,datas!C:Q,15,FALSE))/ABS(VLOOKUP(A6,datas!C:Q,15,FALSE)))
and this is with additional brackets:
=((VLOOKUP(A6,datas!C:P,3,FALSE)-(VLOOKUP(A6,datas!C:Q,15,FALSE)))/ABS(VLOOKUP(A6,datas!C:Q,15,FALSE)))

Thanks.

Glad to help, however I suggest this
=(VLOOKUP(A6,datas!C:E,3,FALSE)-VLOOKUP(A6,datas!C:Q,15,FALSE))/ABS(VLOOKUP(A6,datas!C:Q,15,FALSE))

It follows the () syntax you posted using Cell References.
=(E6-F6)/ABS(G6)