/docs/community?hl=en
/docs/community?hl=en
6/25/11
Original Poster
impipalmer

VLOOKUP function does not work when you include more than 2 rows in the function after last row of data in a sheet.

For example: I have data in 2 rows on a sheet

The following function will report correctly the data you are looking for:
=VLOOKUP("OP 1",A1:AF13,2)
however
=VLOOKUP("OP 1",A1:AF21,2)
complains that the match of "OP 1" cannot be found and you result ends up to be N/A

Community content may not be verified or up-to-date. Learn more.
Recommended Answers (2)
Were these answers helpful?
How can we improve them?
All Replies (13)
ahab
6/25/11
ahab
I can not reconstruct that behavior. But that may be because some information is missing:
a) how many rows has your spreadsheet.
b) how many rows has the lookup table you use in both ranges (A1:AF13 and A1:AF21)
c) are these ranges consecutive, i.e. without blank rows at the top or inbetween
d) is the data in column A in ascending order (required when you use the VLOOKUP like this).

Some simple tips:
- make sure the lookup table does not start with any blank rows
- try to avoid any blank rows in the lookup table
- when you use the VLOOKUP with a fourth parameter set to FALSE make sure the data in the first column of the lookup range is in ascending order; note that VLOOKUP without the fourth parameter or with the fourth parameter set to TRUE performs a best approximate fit.When the fourth parameter is set to FALSE the first column can be in any order and VLOOKUP will look for an exact match.
- try using your VLOOKUP with an open ended range when the data in the lookup table can grow, like this: =VLOOKUP("OP 1",A1:AF,2) or even =VLOOKUP("OP 1",A:AF,2) i.e. where the end row in the table range is not specified or where both start and end rows in the table range are not specified.
6/25/11
Original Poster
impipalmer
Is there a way I can attached the spreadsheet to this for you? To answer your questions
a) At the moment 21, rows 1 to 13 have something somewhere in a row filled in, rows 13 onwards has nothing entered in to any of their columns just yet
b) answer to this is the same as above really
c) the blank rows are the ones that are after row 13, none of the rows 1 to 13 are completely blank
d) data in column A is something like
   Row 1      :   OP 1
   Row 2      :   OP 2
   Row 3      :   OP 3
etc.

Problems with the Tips
- that's good happy and able to do that
- problem is that the spreadsheet will be filled in eventually by multiple people (shared spreadsheet). And for not not all data is filled in. As this will happen over time. I don't want to have to changing to formula's to everytime we get another row added to the speadsheet to update the stat's done on the data
- 4th parameter to the VLOOKUP is not the issue here
- using the open ended ranges is EXACTLY what I would like to do. However am getting the same errors. And in trying to workout what I'd done I went to use the set range with numbers - which is where the issue shows up.

-- other things to note here. The original spreadsheet was created from an Excell import. All the formular are working correctly in Excell its only after importing into Google Docs.
- The issues I point out here :  are me trying to work out why Google Doc's has showed N/A# in almost all the blocks where I'd has formular insterted. And with those for VLOOKUP this is what is happening.


AD:AM
6/25/11
AD:AM
<< 4th parameter to the VLOOKUP is not the issue here >>

Unfortunately I think this often is the issue; there's been a number of instances I've found VLOOKUP/HLOOKUP/MATCH to not behave the same in GSheets as it does in Excel when the fourth argument is TRUE (or omitted).

I understand your concern that it isn't working the same as Excel, but I'd be surprised if including FALSE as a fourth argument doesn't solve the issue, albeit as a "workaround".  And that would still be compatible with Excel.

Cheers
Adam
ahab
6/25/11
ahab
"Is there a way I can attached the spreadsheet to this for you?"
You could help by making a copy of (the relevant part of) your spreadsheet, share it so it is Public to the web and that everyone can edit it; you can post the URL of the spreadsheet in this thread. This allows us to get a more direct look.

Like Adam (TC A.P.L.) says, setting the fourth parameter of VLOOKUP to FALSE may solve a number of unexpected problems especially when multiple people update the lookup table (they may not all be aware of the requirement that with the fourth parameter omitted or set to TRUE the leftmost column of the range must be in ascending order).
6/25/11
Original Poster
impipalmer
GREAT - thank you Adam.  Your are 100% correct adding in the FALSE argument solve's the issue for all accounts.
THANK YOU!!
7 MORE
ahab
6/27/11
ahab
"You might need to explain to me how the binary search is breaking down André, with the odd number of rows etc."
Try this:
 =INDEX( A:A ; MATCH( 1E293 ; A:A ) )
with 
A1: 1
now add in 
A2: 2
now add in
A3: 3
now add in
A4: 4
and so on.

You will see that the formula will return #N/A error: Did not find value 10000000000000000000000000000000000000000000000000000000000000000000000 for an odd number of rows with numbers, and the last value if the number of rows is even. I have no proof that MATCH uses a binary search, but this behavior suggest to me that this may be and because a binary split was not calculated correctly.



 
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.

 
Google Employee — Google product team members and community managers
 
Community Specialist — Google partners who help ensure the quality of community content
 
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
 
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
 
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
 
Product Expert Alumni — Former Product Experts who are no longer members of the program
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.