• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP values

Buckai

Buckai

New Member
Hi. I am using VLOOKUP for first time. It is working and returning the correct value (A $ Price associated with the product looked up from a drop down list. So I get the correct price, but when I go to multiply that cell value with a quantity, I get a #VALUE! result. Do I need to do something to the lookup value first?
Here is the cell formula
Code: 
=VLOOKUP(B16,'data Reference'!A6:B11,2)
(Side Question - did I need to put those tags to include my cell formula in post?)
Thanks, Buckai
 
Hi Buckai

Can we see this file? Is the quantity a value or is it Text? That is my suspicion but without seeing we can't say what the exact problem is. You can multiply the results of a Vlookup no problem so the issue is elsewhere.

Take care

Smallman
 
Thanks Smallman. See workbook attached. In the Estimate sheet I am using Vlookup for a product (Chosen from drop down list) to enter price.(All listed ion datareference sheet) This price is then multiplied by quantity and no days to get total cost. I have set the cell format as number but no difference.It shows correctly as say $800. Hope this helps

And thank you!
 

Attachments

  • Nutshelltemplatedocuments.xlsx
    79.6 KB · Views: 1
Hi Buckai,

Your data is TEXT and not a number. If you want the $ sign, you can format a number as Currency.

I have made one change in the file.

kanti
 

Attachments

  • Nutshelltemplatedocuments.xlsx
    59.5 KB · Views: 4
Hi Kanti. Another issue. In the spreadsheet that i sent you (above), some fields show #N/A as a value when there is no Look Up value. How can I suppress that and just show a blank cell, that becomes a proper numerical value when the other day is looked up and calculated.
 
One more thing. In my lookup, I have the product name in first column,and corresponding price in second column, to the right.
This works until I have a product with the same price as another, Then the second product wth the same price shows a blank value. How to fix???
 
And another. When my sheet tries to calculate the total cost. This is 3 columns multiplied - rate (from the LOOKUP function) x units x days (latter two value manualluy entered) I get nothing showing as the result cell also has the IFERROR formatting, But this answer is not an error, but it seems to consider it as such.
Many thanks
 
here is the file. Go to Equipment Estimate sheet
 

Attachments

  • NutshelltemplateMaster275.xlsx
    82.5 KB · Views: 1
@Buckai,

Few suggestions:

1. Since you are looking values which are not sorted, I would suggest you to use fourth argument of VLOOKUP as 0 or FALSE, which is currently not there.

2. You had entered amount in data sheet as text; don't do it, enter like number say 200 and than custom format it to currency. This will facilitate you for further calculation on this amount.

Regards,
 
Hi guys. I am still having one more problem. When the VLOOKUP is used via my drop down menu om=j estimate shoot, it does not always return the correct price for the selected product. It is correct and present in the look up Table on data reference sheet. It seems to happen when the value (price) has already been entered above for another product selection (The Estimate form allows for multiple products to be chosen from the same range of products. In those cases it returns a blank. Then even less frequently, it returns a completely wrong value. Any ideas?? Latest version uploaded for you. Many thanks.
 

Attachments

  • NutshelltemplateMaster275.xlsx
    82.5 KB · Views: 3
Hi Buckai,

It would have been better if you would have given some examples where you are getting the wrong data. Looking at the formula it's seem that you again missed the fourth argument of VLOOKUP function. Try setting it to 0 or FALSE, as show below as you are looking text which is not sorted. May be this can resolve your issue.

In C24: =IFERROR(VLOOKUP(B24,Lenses35mmARRIUltraPrime,2,0),"")

Regards,
 
You must log in or register to reply here.
Back
Top