Lookup returning incorrect value

[Sherry]

I have a worksheet that has all of my inventory part numbers in column A. I
am using a lookup function requesting that on worksheet 2 it lookup the
partnumber and return the number of units sold in Jan 2007. If the part
number is not on the Jan 2007 page the formula is picking up a number anyway
rather than returning a 0. I'm not really sure why or where it's getting the
number from.

My formula is: =LOOKUP(A103,'2007 January'!A:A,'2007 January'!G:G). Thanks
in advance for your help.

 

[cmungs]

Hi Sherry -

From Excel's help file:

LOOKUP(lookup_value,lookup_vector,result_vector)

If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value.

I would suggest using either VLOOKUP or HLOOKUP to prevent your error.
Assuming your data is arranged in vertical columns, you can use VLOOKUP in
the following way:

=VLOOKUP(A103,'2007 January'!A:G,7,FALSE)

The 7 is the number of colums from left to right that the value you are
returning is from the First column of the data range. FALSE tells excel to
return only EXACT matches.

Does this work for you?

 

[Pete_UK]

Try using VLOOKUP with the fourth parameter set to FALSE (or zero), so
you can get exact matches.

Hope this helps.

Pete

 

[Pete_UK]

If the item is not present, then VLOOKUP will return a #N/A error, and
you will want to trap this and return 0 instead. Do it like this:

=IF(ISNA(vlookup_formula),0,vlookup_formula)

Hope this helps.

Pete