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

How VLOOKUP works!!

Hmm, i get your point from a practical standpoint the method used makes little difference. But since this was the basis of the original question, i was curious to investigate further and thought it might allow for a better understanding of what underpins some of the formulas posted on sites like this one everyday and also an opportunity to look at other non-Excel specific computing topics. In any case, for better and more reliable explanations an expert like Charles Williams would be much better placed to answer this sort of thing.
 
I am getting little confused by the later part of this discussion. LOOKUP function and VLOOKUP (4th arg true) work in similar way. And they require sorted data to be fed to it to give reliable results.

If it is not possible (sometimes such constraints may come up) to sort data then the formula posted by Lori could be used to get reliable results i.e. the same as what a sorted data would give.

Narayan, if you could tell if these were the results you expected? I am attaching the file where I tried it.

The formula I posted was just a play with possibilities but it works and uses both binary and linear searches so I posted.

The formula written by Lori in essence understands "how" binary search works and then uses the understanding to get the desired result.

I could be off the mark with I understood.
 

Attachments

  • LOOKUP_Sort_Unsort.xlsx
    10.7 KB · Views: 8
Hi Shrivallabha / Lori ,

Sorry , my mistake ; I did not go through the discussion , and probably mixed up my testing ; now that your file is available , I can see that the formula which Lori initially gave , as well as yours , works correctly irrespective of whether the input data is sorted or unsorted.

Narayan
 
Was going through this discussion again and realized one more thing:
If we have sorted data then following will be quicker method of finding out the maximum in the range.

=MAX(A1:A10)

Will look into all cells

=LOOKUP(9.99999999999999E+307,A1:A10)

This will look in less cells and will be faster.

For small chunks of data it may not be that significant but with large data sets perhaps it will be more useful.
 
Hi Shrivallabha ,

Just fill up an entire column with random numbers ; =MAX(A:A) returns the value in a fraction of a second.

Narayan
 
Hi Narayan,

Your observation is correct and one more thing I realized is it would be little misleading due to 'normal' usage of function is different. People may not realize (at first sight) that LOOKUP is being used for calculating MAX.

I am diverting from the main point of discussion. Sorry about it.

However, the ability of formula of returning quick (& correct) results shall not be based on applying a single formula. In single formula cases, VLOOKUP will be as good as any other formulas. And of course, I am not saying MAX is bad (same applies to VLOOKUP).

Please try like below:
  • In cell A1, put 1
  • Then in cell A2 put =MAX($A$1:A1)+1 and copy down to row A10000.
  • Measure calculation time.
  • Then in cell A2 put =LOOKUP(9.9999E+307,$A$1:A1)+1 and copy down to A10000
    When I checked calculation times: they were 0.9370003(MAX) and 0.1409988(LOOKUP)
  • The difference become more distinct at very large ranges. For range A1:A100000, it was 146.6719(MAX) and 1.4375(LOOKUP). Difference is noticeable.
I have seen this kind of numbering being used when there are entire blank rows in between and user wants to implement some kind of serial numbering.
 
Is there an easy way to calculate the column number needed? ie I know the column is CD, is there a way to see what number column that is for VLOOKUP purposes or do I need to keep manually counting across?
Thanks

Dubs
 
Hi Dubs,

When you build up your formula, please pay attention to the information Excel shows. While inputting VLOOKUP's table_array argument Excel shows the dimension of the array at the bottom of the dynamically selected table like: 10R X 6C which means the array is of 10 rows and 6 columns.
 
Is there an easy way to calculate the column number needed? ie I know the column is CD, is there a way to see what number column that is for VLOOKUP purposes or do I need to keep manually counting across?
Thanks

Dubs

@Dubs

You should start your own thread.

If you need to get column number of any column Try:
=COLUMN(CD1)

But make sure it will not be same with your array column number.
Regards,
 
Back
Top