yogi_Look At Implications Of LookUp Range Sorted In Asc Order In Use With The VLookup Function

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #623   Jul 13, 2012     www.energyefficientbuild.com.


user bryan-p said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/M9nw3LdF4bQ)
VLookup only works for a certain number of rows 
Why does the VLookup formula in col B on this sheet only work up to row 13 in the array it's referencing?

=VLOOKUP(A4,'Q1'!$A$1:$E$14,1) 

----

when I responded that

it doesn't work because you need to use the the 4th argument as 0 ... so

try using

=VLOOKUP(A4,'Q1'!$A$1:$E$14,1,0) 

bryan-p then said

But isn't my formula for that table already sorting the data in ascending?

=ARRAYFORMULA(SORT(IF('Form Data'!E2:E = "","",'Form Data'!A2:F),1,TRUE,2,FALSE))  

----

Well, my take is ... NO ... not really since it has not been truly sorted in Asc order, I have chosen to call the SORTing performed by the SORT function as SweetenedSort (I could have also called it by some other choice words ... but let us play nice) as I discuss it in the following. That is why in working with the LookUp range SweetSORTed in Asc order I still have to use the 4th argument of 0 because the Lookup range SweetSORTed in Asc order is not truly sorted in Asc order.