Excel 2007 : Vlookup not matching all the product codes.

**aLc319** · 06-24-2009, 01:42 AM

Good afternoon all,

The spread sheet I'm working on is acting weird. Some products can be located and some can't.

Eg.
My worksheet "Westlake" has Product code in column A with the months after it. My other worksheet "Jun" has all the quantity sold with Product code in Column A as well.

However, some product codes works and shows up the numbers, but some aren't. The formula I'm using is:

=IF(ISERROR(VLOOKUP($A5,Feb,5,FALSE)),"",VLOOKUP($A5,Feb,5,FALSE))

Can anyone help?

**DonkeyOte** · 06-25-2009, 02:43 AM

Might be worth checking the size of your named range in relation to the actual data you have (ie Jun does not encompass all the data on Jun sheet)

**aLc319** · 06-25-2009, 05:17 PM

Thanks for the feedback. Jun worksheet does not need to have all the data in the Westlake worksheet, because the data in Jun is all the products that a specific customer has bought (customers don't buy all the products).

And when I checked a product appeared in Jun, the Westlake worksheet is not showing up the result. =(

**DonkeyOte** · 06-25-2009, 05:22 PM

Not my point - your Westlake sheets makes use of a Named Range - Jun .. the Named Range does not actually encapsulate all of the data you have on your Jun worksheet meaning your Westlake sheet is not referencing all of the data meaning it can't pull back all of the information...

**aLc319** · 06-25-2009, 05:54 PM

Oh ok. So you're saying that Westlake's formula is not including all the data? If that's the case, how do I fix it? Thanks for your help, really appreciate it =)

**DonkeyOte** · 06-25-2009, 05:59 PM

Correct the Named Range RefersTo range for "Jun" named range...

Presently the RefersTo formula is set as:

=Jun!$A$1:$N$100

obviously your data exceeds row 100... you could enlarge to say 200 but better yet may be to use Dynamic Named Ranges... if for ex. we assume the last cell in A containing text represents the last row of data to encapsulate we can use:

=Jun!$A$1:INDEX(Jun!$N:$N,MATCH(REPT("Z",255),Jun!$A:$A))

as you add data so the named range will expand automatically and you should find your Westlake sheets results update accordingly (from 444 to 961)