VLookup only works for a certain number of rows | bryan-p | 7/12/12 8:56 PM | 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) | |||||

Re: VLookup only works for a certain number of rows | Yogi Anand | 7/12/12 9:13 PM | Hi bryan-p:
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) | |||||

Re: VLookup only works for a certain number of rows | Gill | 7/13/12 9:55 AM | Could you please explain why, Yogi? What is the 4th argument for? I struggle to get my brain around VLOOKUPs although they are incredibly useful and I do use them - with help! Thanks. | |||||

Re: VLookup only works for a certain number of rows | Yogi Anand | 7/13/12 10:01 AM | Hi Gill: If the LookUp Table is not sorted in ascending order and an exact match is sought, use of FALSE or 0 as the 4th argument is necessary. | |||||

Re: VLookup only works for a certain number of rows | Gill | 7/13/12 10:09 AM | Aha! Thanks Yogi :-) | |||||

Re: VLookup only works for a certain number of rows | bryan-p | 7/13/12 10:28 AM | If the LookUp Table is not sorted in ascending order 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)) | |||||

Re: VLookup only works for a certain number of rows | AD:AM | 7/13/12 1:45 PM | On Saturday, 14 July 2012 03:28:12 UTC+10, bryan-p wrote:If the LookUp Table is not sorted in ascending order Yes it is, and the VLOOKUP with the fourth argument TRUE (or omitted) will fail when there are a certain number of blank rows in the referenced data. It should indeed work, and it doesn't due to a long-standing bug in Google Spreadsheets. The same VLOOKUP will work in Excel, for example. In your particular use case, it would seem you require an exact match anyway, so using FALSE (or 0) for the fourth argument would be reasonable. However it was generally accepted/recommended in Excel that the "binary lookup" used when the data is sorted and the argument is TRUE was more efficient (better performance) than when the argument is FALSE. I really don't know whether this holds true for GSheets. | |||||

Re: VLookup only works for a certain number of rows | Bas Braams | 7/13/12 2:59 PM | <<... the VLOOKUP with the fourth argument TRUE (or omitted) will fail when there are a certain number of blank rows in the referenced data>>. I tried to make sense of this, and I find the behavior strange even if there are not any blank rows. The 4th argument to the VLOOKUP function is described as follows in the help pages: "Sort_order (optional, defaults to TRUE) indicates that the first column in the array is sorted. If this is true, then VLOOKUP will return the closest match to the search_criterion. If false, then only exact matches will be returned." I open a new blank spreadsheet and populate the A1:B2 upper left 2*2 corner as follows: (b, 0; e, 1). So (b, 0) in the first row, (e, 1) in the second row. In a different cell I try =vlookup("x",A1:B2,2,true()) and then I vary the value of x. If x=b or x=e then I get the expected result, 0 or 1 respectively. The interesting case is when x doesn't occur in the array, so x isn't equal to b or to e. If x is equal to c or d then the result is 0, as if "b" is the closest value. I have no strongly held opinion about what means "closest"; for all I know it might have been "e" if x is equal to d. If x is equal to f, g, h, ... then the result is 1, as if "e" is the closest value. This makes sense to me in any case. If x is equal to "a" then the function returns #N/A. For this case I would have expected a result 0 under any reasonable interpretation of "closest". | |||||

Re: VLookup only works for a certain number of rows | AD:AM | 7/13/12 3:29 PM | On Saturday, 14 July 2012 07:59:49 UTC+10, Bas Braams wrote: I fell this particular issue is more about error in documentation Bas, rather than unexpected behaviour. As far as I know and have tested, VLOOKUP with the TRUE 4th argument attempts to mimic Excel/Open Office/Libre Office etc. So the documentation should actually read: "If this is true, then VLOOKUP will return the greatest match that is less than or equal to the search_criterion." | |||||

Re: VLookup only works for a certain number of rows | Yogi Anand | 7/13/12 5:06 PM | Hi bryan-p, Adam, and Bas: I have a little different take on it ... I think the SORT function as it sorts in Asc order by pushing the blanks to the bottom of the populated rows is not truly sorting in Asc order ... I would like to call this way of sorting in Asc order as the SweetenedSort in Asc order. I have played with different scenarios in my following blog post: yogi_Look At Implications Of LookUp Range Sorted In Asc Order In Use With The VLookup Function Please have a look and let me know what you think, | |||||

Re: VLookup only works for a certain number of rows | AD:AM | 7/13/12 5:27 PM | On Saturday, 14 July 2012 10:06:11 UTC+10, yogia wrote: Hi Yogi Yes that's certainly the case with GSheets - the VLOOKUP with a TRUE 4th argument expects blank cells to be before non-blank cells. It's just that this is different behaviour to Excel. Excel will effectively "ignore" blank cells anywhere in the lookup array, even with the 4th argument set to TRUE (or omitted). I'm not suggesting one way or the other is the "correct" way, it's just that some users migrating from Excel perhaps expect the same behaviour, and get tripped up. | |||||

Re: VLookup only works for a certain number of rows | Bas Braams | 7/14/12 3:45 AM | <<I think the SORT function as it sorts in Asc order by pushing the blanks to the bottom of the populated rows is not truly sorting in Asc order>> It looks that way indeed. I think that the Docs Spreadsheet designers missed an opportunity here to do it right. Following the logic of lexicographical ordering (or the mathematics) the empty string should come first. (Just as "a" comes before "aa", so "" should come before "a".) However, the GSheets SORT function pushes empty cells to the end and it does that for ascending sort as well as for descending sort. Note that an empty cell is different from a cell that contains one or more blanks and nothing else; those cells seem to be treated correctly in the lexicographical sort, with the blank being (apparently, upon quite cursory inspection) the first symbol in the lexicographical order. For the case of sorted array VLOOKUP with 4th argument TRUE would, as pointed out by APL+, locate the last row for which the key is less than or equal to the search value. That makes VLOOKUP undefined if the search value is less than the smallest key. It is therefore useful to have access to a value that comes before all other values in the sort. The empty string would be the most natural choice, but as it is in Google Spreadsheets it has to be the string " ", containing a single blank. | |||||

Re: VLookup only works for a certain number of rows | Bas Braams | 7/14/12 11:43 AM | Just to have things together here let me point to an earlier conversation [1] that is related to the present one. On that conversation it is pointed out that different spreadsheet programs may use a different collating sequence. VLOOKUP with 4th argument TRUE assumes that the first column is sorted, but "sorted" has then a different meaning depending on the program and this can be a source of trouble if one uses in Google Spreadsheets a sheet that was imported from Excel. For the record, the sorting order for Google Spreadsheets appears to be the following: (blank) _-,;:!?.'"()[]{}@*/\&#%`^+<=>|~$0...9a...z(single quote immediately before the double quote). Lowercase and uppercase letters are not distinguished for sorting purposes. The treatment of accented letters seems to depend on the locale. [1] (2011-06-25) VLOOKUP function does not work when you include more than 2 rows in the function after last row of data in a sheet | |||||

Re: VLookup only works for a certain number of rows | Yogi Anand | 7/14/12 11:46 AM | Hi Bas Bramms: 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). 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. In my blog post, I showed that using the QUERY function and then ORDERing items in Asc order pulls the empty rows then followed by populated rows ... and if I work with LookUp range thus truly sorted in Asc Order (and not simply with SweetSorted in Asc order), I can use the VLookup function without having to use the 4th argument of 0. Google Guides and Engineers:Let us have your take on this please ... I am also going to bring it to the attention of Teresa Wu. | |||||

Re: VLookup only works for a certain number of rows | Bas Braams | 7/14/12 12:08 PM | <<and if I work with LookUp range thus truly sorted in Asc Order [...] I can use the VLookup function without having to use the 4th argument of 0.>> I don't quite follow this. I tried to create a simple case. My spreadsheet contains A1: A2: b B1: 0 B2: 1 To be clear, the A1 cell is truly empty, it does not contain a space character. The way I understand Yogi's remark, this A column is "truly sorted in Asc order" and therefore I should be able to use the VLOOKUP function with 4th argument TRUE. So we try it. In a different cell I enter the formula =VLOOKUP("a",a1:b2,2,true()) The expected result is 0, because in the "true" Asc sorting order the letter a comes after the empty string and before the letter b. However, the actual result is #N/A! If the A1 cell is changed to contain a single space then the vlookup function as written returns 0. | |||||

Re: VLookup only works for a certain number of rows | AD:AM | 7/14/12 2:34 PM | To be clear, there are two separate sorting issues here (IMHO): 1. How GSheets sorts values with the SORT function and the built-in sorting tool (in ascending order: numbers including dates, then strings lexicographically and case-insensitively as Bas described, then Boolean values (FALSE then TRUE), then errors, and then blank cells and zero-length text strings - which are not distinguished) 2. How VLOOKUP (and HLOOKUP and MATCH for that matter) treats a sorted range when the TRUE argument is used - and how this differs from other spreadsheet applications Regarding 1, I personally have no issues with pushing blanks to the bottom, even if it might be mathematically inaccurate. Watch the forum light up if SORT started sorting blanks to the top, hahaha. And as Yogi pointed out, we have QUERY as an option if you wanted "blanks to the top". Regarding 2, I think this is perhaps the bigger "issue" (?). In Excel, blank cells and error cells are simply ignored, no matter where they appear in a "sorted" range. And as Bas pointed out with his example, even if VLOOKUP in GSheets requires "blanks at the top", it still appears to be broken. | |||||

Re: VLookup only works for a certain number of rows | Bas Braams | 7/15/12 3:48 AM | Still with a view to keeping things together I like to repeat here a report from APL+ and ahab on the earlier cited conversation [1]. I've changed the instructions a bit to avoid some distractions. Open a new empty spreadsheet and enter the number 1 in the A1 cell. In a cell in a different column enter the formula =MATCH(100,A:A). The result in Google Spreadsheets is #N/A!. Now start adding numbers in the A column, one by one and maintaining order. For example: A2: 2 A3: 3 A4: 4 A5: 5 and so on. It will be found that the MATCH function "works" (returns the location of the last entry that is less than or equal to 200) whenever the number of entries in the A column is even and it "fails" (returns #N/A!) whenever the number of entries in the A column is odd. To reinforce the observation, now use all entries equal to 0 in the A column and use the formula =MATCH(1,A:A). If there is an odd number of entries then one gets #N/A! whereas for an even number of entries one gets the index of the last entry. The construct "A:A" seems to be undocumented for Google Spreadsheets, so I don't know what to say about these observations. If the A:A is replaced by a fixed range then the observations are different, but they are also chaotic. I'll use a separate response for that. | |||||

Re: VLookup only works for a certain number of rows | Bas Braams | 7/15/12 4:34 AM | The title of this conversation refers to the VLOOKUP function, but things are interconnected and therefore I don't mind to make some observations about the SORT function, the MATCH function and treatment of blank cells in the same conversation. Here are some oddities with the MATCH function. Let A1: 0 B1: =MATCH(0,A1:A1) It returns the value 2 in the B1 cell. (Expected result: 1) Now let A1: =0 B1: =MATCH(A1,$A$1:A1) then autocomplete it down to A80 and B80 (A80: 0; B80: =MATCH(A80,$A$1:A80).) Expected result: Each B[n] should acquire the value n. Actual result: B1=2, B2=2, all other B[n]=n+1. Now let A1: =1 B1: =MATCH(A1,$A$1:A1) and autocomplete it down to A80 and B80. It is essentially the same example as before, but we are now looking for the value 1 instead of the value 0. Expected result: Each B[n] should acquire the value n. Actual result: B[n]=n+1 always except B80=80. Now let A1=0, all other entries in the A column blank, and let B1: =MATCH(0,$A$1:A1) and autocomplete only the B column down to B80 (=MATCH(0,$A$1:A80).) Expected result (if blank keys would be ignored): Each B[n]=1. Actual result: B1=2; B2, B3, B6, B7, B14, B15, B30, B31, B62, B63 all equal to 1; all other B[n]=n+1. Now let A1=1, all other entries in the A column blank, and let B1: =MATCH(1,$A$1:A1) and autocomplete only the B column down to B80 (=MATCH(1,$A$1:A80).) Expected result (if blank keys would be ignored): Each B[n]=1. Actual result: B1=2, all other B[n]=1. | |||||

Re: VLookup only works for a certain number of rows | AD:AM | 7/15/12 3:32 PM | Adding to Bas' last post: 1. The "there must be no such thing as a table with only one row" bug: A B 1 a b Observe the results of: =VLOOKUP("a";A1:B1;2) =VLOOKUP("a";A1:B1;2;0) =MATCH("a";A1) =MATCH("a";A1;0) (this one works)2. The "invisible horizontal data" bug: A B C 1 1 2 3 Observe the results of: =MATCH(2;A1:C1) =MATCH(2;A1:C1;0) (this one works) | |||||

Re: VLookup only works for a certain number of rows | Bas Braams | 7/17/12 5:23 PM | <<The "there must be no such thing as a table with only one row" bug>> and <<The "invisible horizontal data" bug>>. Those are nice minimalist examples. | |||||

Re: VLookup only works for a certain number of rows | Paul Wright7012 | 8/20/13 11:37 AM | I have a similar problem =VLOOKUP([CPT],'CPT Codes and Fee''s'!$G9:$I19,3,FALSE) This will only work on the first 3 rows then I get an #NA. It was working but something went wrong. Any Ideas | |||||

Re: VLookup only works for a certain number of rows | AdamMcKenna | 9/23/13 9:47 AM | Why in the world is this the default? The default should be an exact match, that's how it is in Excel and what will be expected by Google Spreadsheets users. | |||||

Re: VLookup only works for a certain number of rows | Yogi Anand | 9/24/13 5:56 PM | Hi AdamMcKenna: Please correct me if I am wrong ... I submit that the default in Excel (as in Google spreadsheet) is also with 4th argument as 1 or TRUE ... for non-exact Match
| |||||

Re: VLookup only works for a certain number of rows | AdamMcKenna | 9/25/13 1:52 AM | OK maybe I'm remembering wrong then, because I haven't used Excel in a while, but I could have sworn that I've never had to put 0 or FALSE in a vlookup to make it return the right value. |