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

 

[Richard Schollar]

Hi Shay

If you are getting a #N/A it means Vlookup thinks it can't find the data. There are several possible reasons for this eg trailing spaces or numeric data vs Textual data. You will have to go over both your lookup values and your looked up range data in detail.

 

[shaylynch]

Hi Richard,
thanks for your reply, all the info is coming from the same place so the data found by the hlookup is the same data used in the vlookup to return the remainder of the info, i have already done as you suggested and went through everything with a fine tooth comb

thanks again

Shay

 

[shaylynch]

if someone actually has a different way of doing this i would be delighted to hear it

cheers

Shay

 

[supdawg]

Can you post your formula here?

 

[shaylynch]

here are 2 formulae that i have tried and both coming up with the same error
=VLOOKUP(IF(HLOOKUP($H$71,SPACKS,5,FALSE)=" "," ",HLOOKUP($H$71,SPACKS,5,FALSE)),$H$131:$CN$151,3,FALSE)
where H71 is title & SPACKS is the range containing all pack info

=VLOOKUP($H73,$H$131:$CN$151,3,FALSE)
where H73 is the description cell

Cheers

Shay

 

[konew1]

Check that your HLOOKUP to " " is correct. I think it should be "" (without a space).
If the HLOOKUP does return " " (with a space) that means your VLOOKUP is searching for an entry in the data list that is just the space character

 

[shaylynch]

thanks for that however it didn't work, it's a good point though

Shay

 

[supdawg]

Is your data numbers?

have you tried multiplying all of your numbers by 1 just to make sure?

 

[shaylynch]

i have yes, it's a real strange one, i really don't know why this is happening, all info was created the same way