• 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.

Difference in Lookup

Asheesh

Excel Ninja
Hi Guys...

Please see the attached...

I have lookup formulas in cell B1 and C1..I just want to know the difference...coz both return the same result...

I always use the one in cell B1 and never came across any issue as such...
 

Attachments

  • Difference.xlsm
    7.7 KB · Views: 8
Both find the last cell with a value less than either "z" or "zzzzz etc"
in either case it is the cell with pp in it
 
Hi Asheesh ,

There is really no difference ; what you should understand is that the lookup value which is used as the first parameter in the LOOKUP function should be greater than any of the data values which occur in the second parameter of the LOOKUP function. That is all that is required.

Try the following variations on the LOOKUP formula in cell B1 :

=LOOKUP("p",$A:$A)

=LOOKUP("po",$A:$A)

=LOOKUP("pp",$A:$A)

=LOOKUP("pq",$A:$A)

=LOOKUP("q",$A:$A)

Using the technique in cell C1 is guaranteed to return the last value , since it is very unlikely that your data will contain an item such as "zzzzzz...." where the letter z is repeated 255 times.

The same technique is used in numeric data when the first parameter of the LOOKUP function is 9E307 , which is the largest number possible ; if you are sure your data will never exceed 2 digits , even using 100 for the first parameter will produce the same result.

In cell B1 , if you enter :

=LOOKUP(9E307,$A:$A)

the result will be 21.

Narayan
 
Back
Top