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

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:

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

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

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

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.to=VLOOKUP(C2;ResponseIDsA$1:B$9; 2; FALSE)

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

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

Thank you for this, I was in almost the exact same situation.

ReplyDeleteGlad you found it helpful and thanks for stopping by!

DeleteThank you so much. Finally an explanation of vlookup in language I can understand. I needed to add a ! after the worksheet name before it would work for me. Your explanation saved me a lot of time.

ReplyDeleteAlso glad that you found this helpful! I still wish the language could be easier, but I'll keep working on it! I'll also double check on the "!". Thanks for the heads up.

Delete