| 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: |
| 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 | 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 | APL+ | 7/13/12 1:45 PM | 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 |
| 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 4t |
| Re: VLookup only works for a certain number of rows | APL+ | 7/13/12 3:29 PM | 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 |
| 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 so |
| Re: VLookup only works for a certain number of rows | APL+ | 7/13/12 5:27 PM | 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 |
| 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 |
| 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 argu |
| 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 |
| 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: bB1: 0B2: |
| Re: VLookup only works for a certain number of rows | APL+ | 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 Ba |
| 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 |
| 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 |
| Re: VLookup only works for a certain number of rows | APL+ | 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 B1 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 "invi |
| 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. |