7/13/12

Original Poster

bryan-p# 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)

Community content may not be verified or up-to-date. Learn more.

All Replies (23)

7/13/12

Yogi AnandHi bryan-p:

On Thursday, July 12, 2012 11:56:41 PM UTC-4, bryan-p wrote:

On Thursday, July 12, 2012 11:56:41 PM UTC-4, bryan-p wrote:

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)

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)

7/13/12

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

7/13/12

Yogi AnandHi 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.

7/13/12

GillAha! Thanks Yogi :-)

7/13/12

Original Poster

bryan-pIf 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))

7/13/12

AD:AMOn Saturday, 14 July 2012 03:28:12 UTC+10, bryan-p wrote:

If the LookUp Table is not sorted in ascending orderBut isn't my formula for that table already sorting the data in ascending?

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.

7/14/12

Bas Braams<<... 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".

7/14/12

AD:AMOn Saturday, 14 July 2012 07:59:49 UTC+10, Bas Braams wrote:

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

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."7/14/12

Yogi AnandHi 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,

7/14/12

AD:AMOn Saturday, 14 July 2012 10:06:11 UTC+10, yogia wrote:

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.

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.

9/25/13

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

Were these replies helpful?

How can we improve them?

This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

### Badges

Some community members might have badges that indicate their identity or level of participation in a community.

Community content may not be verified or up-to-date. Learn more.

### Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

- Post an answer.
- Having your answer selected as the best answer.
- Having your post rated as helpful.
- Vote up a post.
- Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

### View profile in forum?

To view this member's profile, you need to leave the current Help page.

### Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

### Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.