• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Numerical Value using LOOKUP Function --- it is not working

I did following
=ISNUMBER(-RIGHT(B2,ROW($1:$4)))

It returns:
{TRUE;TRUE;TRUE;TRUE}

so it should give the last value, which is 7000.
But it is giving me 0...

Why do you think this is...?
 
@Narayan,
Wow thank you for your advice.
I am getting confused here but I do not understand this.
So it takes '0" as exact match?

I see two solutions here

=-LOOKUP(1,-RIGHT(B2,ROW($1:$4)))

Or

=-LOOKUP(0,-RIGHT(B2,ROW($1:$7)))

*But this last one I do not know why it works...
 
Hi ,

The job of the LOOKUP function is to return a matching value ; it returns the last value in the array / range because it is unable to find a match ; it is unable to find a match because we have selected the appropriate lookup value.

For positive numbers , we select a high enough lookup value , which we are sure will not be present in the array / range.

For negative numbers , we can use 0 only if we are sure that all the negative numbers in the array / range are non-zero. If zero is a possibility , then we need to select some other number which will be bigger than all the possible numbers in the array / range.

As you have found out , 1 will do the job , since apart from 0 , all the other numbers will be negative , and they as well as 0 will be less than 1. This will ensure that the LOOKUP function will not find a match and hence will return the last value.

Narayan
 
@NARAYANK991

Hi thank you for explanation.

1. Sorry to be pain.
I do not want to waste your time.

But to use this technique I need to explain to my colleague
why range is $1:$255
So I tried to simplify it.

2. I thought about using below too.
indirect("$1&"LEN(B2))

3. This is still baffling me why below returns 7000
*But this is not so important, as my problem is solved by your amazing support...

=-LOOKUP(0,-RIGHT(B2,ROW($1:$7)))

screenshot.png
 
Hi ,

I am baffled by why with 7 it works ; I can see that 8 , 9 and 10 do not work , but using 11 works !

It is something to think over , but not at this time of the night.

Thanks for this.

Narayan
 
Hi ,

You can use the INDIRECT function , but the parameter needs to be a string ; this is valid , but again , does not work in this case.

=-LOOKUP(,-RIGHT(B2,ROW(INDIRECT("$1:"&LEN(B2)))))

Narayan
 
You must log in or register to reply here.
Back
Top