Highline Excel Class 42: Versatile LOOKUP function 10 Examples
See these 10 LOOKUP tricks 1.LOOKUP: 1) 1st column sorted ascending, 2) only does approximate lookup, 3) always returns value from last column / row, 4) can do V or H Lookup, 5) can have 2 vectors 2.More rows than columns: LOOKUP does vertical lookup 3.More columns than rows LOOKUP does…
Hi Mike
Around 7:42 in this video you sorted the first column (PRODUCT) and said LOOKUP can only do a approximate match and thats why we get a wrong answer “BOOM 1″, but if thats the case then how did LOOKUP give us the correct value when we did it the first time, i.e. how did LOOK UP initially pull in “BOOM9″ for the look up value “Flying Range is 160″ what changed after we sorted the first column?
Regards
Chor
Before we sorted the Description column was sorted (lookup column) and so it worked. After we sorted the Product column, then the lookup does not work because LOOKUP takes “Flying Range is 160″ and as soon as it sees “Flying Range is 20″, it stops looking any further. It thinks that there are no more entries that have a “is 1″ near the end of the text string.
Alphabetically we shoud have: 1st “Flying Range is 10″ and 2nd “Flying Range is 160″.
Hi Mike
You are right i dint notice that the Description column is sorted, thanks very much for the reply
Regards
Rajesh
You are welcome!
Mike
Need your help again, just wanted to know what would happen if the search function returned two numbers for e.g if instead of this{1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} lets say we had {1;#VALUE!;#VALUE;17;#VALUE!;#VALUE!}
what would the lookup function return, would it return both the instructors name or just any one.
Thanks for all the help again
Regards
Chor
Iam sorry Mike i just realised that i have not mentioned the worksheet name its actually L4
Regards
Chor
It will always return the biggest (in your example 17).
Thanks Mike.
Regards
Chor