Functions and Formulas

Re: VLOOKUP Function

Nick Hodge — Mon, 09 Jun 2008 07:18:40 GMT

Sherrie Ann

Reading this again on the train you could be confused with my 'paraphrased' function. By WhereToLookUp, It must include the column you wish to draw the value from as well, e.g, you would not just enter $A$1:$A$100 with an offset of 2. That would need to be $A$1:$B$100

Sorry if that confused?

Re: VLOOKUP Function

Nick Hodge — Mon, 09 Jun 2008 05:53:22 GMT

Sherrie Ann

VLOOKUP will go from top to bottom and then when it finds a match it will take the value from x number of columns to the right. (Signified by the offset attribute).

Therefore if you are sure that the match you want to find is the first from the top, that is the value that is returned.

VLOOKUP has a last parameter of TRUE or FALSE. FALSE will find exact matches only and return an #N/A error if not match is found. TRUE relies upon the data being sorted in ascending order. It then finds either the first exact match or the largest value that is nearest to the exact match in value (Either largest text value or numeric). If you do not put a last parameter it is the same as TRUE

=VLOOKUP(WhatToLookUp,WhereToLookItUp,HowManyColumnsToOffset,TRUE/FALSE)

VLOOKUP Function

Sherrie_Ann — Mon, 09 Jun 2008 00:24:45 GMT

I'm new to Excel and have a question on the VLOOKUP function. In a worksheet, I have a VLOOKUP function returning a quantity value for an ID. My worksheet contains a long list of ID numbers, some of which are duplicates. I only want to return the quantity value from my other worksheet for only the first occurrence of the ID number. If the ID number is repeating, I do not want the quantity value to return. I'm not sure if this is possible, but thought I'd ask the experts! Thank you!