vlookup returning #N/A when it shouldn't

[shaylynch]

Hi all,
i am having the most annoying problem with vlookup and any help would be greatly appreciated!!! i sell solar panel systems and i've broken it down to 18 different packs that i can sell, the reason for this is that i can very quickly have a tailored quote for a particular client. what i want to do is is literally from a pull down menu have the 18 packs, pick one and for all elements of that quote to come up. i have used a data validation list for the pull down menu which works fine. for the 18 packs, each one has an array of info of between 15 - 18 rows and 4 columns wide (containg info like part description in first column, then cost in second column, quantity in third column and total cost in fourth. how i set it up was, i have all the seperate packs going for left to right on a spread sheet with the title name on top. i have created a quote area so when i use the pul down menu to get title all that pack info should come up. i have done this by using the hlookup command to return the first column on the left - description on parts and this works fine - then to return the cost in the second column, i am using a vlookup command by looking for the description on the left to return the cost. this works for 90% of the info and for the other 10% i get an #N/A when i know it shouldn't. all cells have the same format so i know this isn't the problem. i would really appreciate any help on this from anyone who has had this problem before, it's breaking my heart!!!
thanks in advance

Shay

 

[Titian]

A couple of suggestions from me:

Have you checked that "SPACKS" contains the data (ranges etc) that you expect?

Are your lookup ranges "$H$131:$CN$151" correct?

 

[shaylynch]

thanks Titian,
i have, its all correct, i've checked everything a million times at this stage
thanks for you input

Shay

 

[John Davis]

thanks Titian,
i have, its all correct, i've checked everything a million times at this stage
thanks for you input

Shay

One more if nothing else worked. Try data, text to columns, finish - on the column with the formulas.

 

[supdawg]

If your worksheet does not contain any sensitive data, perhaps you can post here, and have another set of eyes look at it up close in personal.

 

[arkusM]

In your formula:

 =VLOOKUP(IF(HLOOKUP($H$71,SPACKS,5,FALSE)=" "," ",HLOOKUP($H$71,SPACKS,5,FALSE)),$H$131:$CN$151,3,FALSE)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

the "3" is causing you grief.<o></o>
The three is not going to be "Dynamic" as you might think it should be. It is not going to the "n"th product and pulling the third col. if you Hlookup evals to false and pulls the correct descriptor your vlookup will ALWAYS pull the 3rd col which would be col J. Which would explain why sometimes you get the N/A# but not always.
<o></o>
I would use a Offset match instead generically it looks like<o></o>

 =OFFSET(ref,MATCH(row variable, "single col row search",0),MATCH(header variable, "single row header search",0))

<o></o>
<o></o>
and it generally it works like a vlookup but gives you the col variation you need.
But I would need to see a small sample of you data to make it work in your case. It is not as a difficult formula as it make look.
<o></o>

MR<o></o>

 

[shaylynch]

Thanks arkusM,
that is exactly what the problem was, i did a number of checks and that was exactly it, i simulated a test to confirm, i have now realigned the data in another format and i have it working. thanks to all who assisted, i really appreciate it!!!¬!

Shay

 

[arkusM]

You may want to look at the "5" in you Hlookup as well, as it is not dynamic and may cause you grief.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
The return col. In the lookups are the limiting factors, but if you can get the OFFEST matches to work they allow for some flexibility. The upside to v/hlookups is that if you reference cells outside your workbook they will hold the looked up value even if the sources sheet is closed. I have not had consistent results in terms of updating with the sheets closed, but the formulas don’t blowup.<o></o>
The down side to the OFFSET/Match is that the source sheet must be open for a calculation, if the second sheet is not open then your formulas will error out.<o></o>
<o></o>
<o></o>
<o></o>