VLOOKUP in Google Docs and the importance of ordering an array

I spent hours trying to figure this out so here's this post.  I was trying to populate the names and email addresses from one Google worksheet (in the same spreadsheet) to another worksheet.  In other words, I had two worksheets-- let's call them MyForm and ResponseIDs.  MyForm contained only nicknames while ResponseIDs contained the actual names along with nicknames and email addresses.  I wanted to retrieve the corresponding names, email addresses, etc. from ResponseIDs to MyForm based on nicknames.  I know that there are multiple ways to do this, but I decided to just go with a simple (or so I thought) VLOOKUP function-- hah! famous last words!


In a previous post, I explained that a typical VLOOKUP syntax might look something like this:

=VLOOKUP(C2;GradeScaleA$1:B$9; 2; true) 

Where

• C2 = look at C2 where the numeric grade is (the Calculated Grade column; 87, 92, etc.) 

• GradeScale = the name of the sheet where my Grade Scale table is located

• A$1:B$9 = the actual array of my table (***IMPORTANT: make sure you use the $ before the numbers-- otherwise each time you drag the formula, you'll change your array!  This is called absolute referencing,)

• 2 = return the value in column 2 (if you enter 1, it'll return the value in column 1) of the GradeScaleTable

But when I tried to use this same syntax here, it just would NOT return the correct values...and after much searching I finally stumbled upon the following here: 

When using no fourth parameter or as fourth parameter TRUE VLOOKUP does a nearest value lookup and expects the first column of the range it references to be in ascending order. By using as fourth parameter FALSE VLOOKUP does an exact lookup and allows the first column to be in random order. (emphasis is mine)

In other words, because my ResponseIDs worksheet was not in ascending order; my original syntax resulted in only a "nearest parameter search" and the names and emails it retrieved were wrong.  To fix it, I simply had to change TRUE to FALSE:

from =VLOOKUP(C2;ResponseIDsA$1:B$9; 2; true) 

to   =VLOOKUP(C2;ResponseIDsA$1:B$9; 2; FALSE)

Note that if you're looking for a range of values (for example for a gradebook), you probably want to use TRUE because you're NOT necessarily looking for an exact match; rather, you're usually looking for something like any number between 80-83= B, etc.


Finally, I also realized that you could retrieve any column you wanted.  My emails were in column 3 so I just changed the syntax to this: =VLOOKUP(C2;ResponseIDsA$1:C$9; 3; FALSE).  Again, here's the breakdown:

• C2= the cell to use (in this case, the nickname cell in MyForm)
• ResponseIDs= the name of the worksheet to look in for my array data
• A$1:C$9= the array (in stead of checking only two columns (A and B), it checks three columns A, B, C))
• 3= return the value from column 3 of the array
• FALSE= find an exact match to C2

I did this for each column where I wanted to retrieve a certain piece of information.  


As a final note, I should point out that the Google Forum where I finally found this brilliant information also pointed out another possible syntax to use:

Try using e.g.: Slots!E2: =vlookup(D2,DataFields!A:C,{2,3};FALSE) 

and

Note the third parameter being the embedded array {2,3} allows VLOOKUP to return values from but the second and third column of the referenced range, thus allowing the user to use less - costly, as in slow - VLOOKUPs.

This syntax seems to indicate that it would be possible to populate columns 2 and 3 automatically, but I can only guess since I didn't try it out.  Anyway, I hope this was helpful...