Vlookup giving wrong data please help

[Afrabat]

I am using a vlookup in conjunction with data validation and named range.

The table being referenced is a product list. Two columns. First column is the name of the product 62 cells long, and the second column is a price formatted as currency.

small sample of list:
Hunter Pro C $200
Hunter PGP $29

etc... 62 cells long. I even sorted it name.

When I use the VLookup, data validation and named range. And I know I'm doing the correct formula with absolute references. It gives me random data from halfway down the lists. I don't have the exact formula with me today but will have it tomorrow night. I was using =vlookup(prodname,products!$A$2;$B$61,2,false)

That is what I think it was off the top of my head, but I could have a few characters wrong. It was supposed to reference the price of the part when I click it in the list selection.

Also, I had another question. When it does work correctly, when I change the product in the list, will it automatically change the price instantly? When it seemed like it was working the first time, I changed the list and the vlookup data stayed the same instead of instantly changing.


Any help would be greatly appreciated!
Thanks in advance for helping the new guy!



Matt

[NBVC]

There should be a colon : not a semi colon ; in the Products!A2:B61 range reference.

check that you have Automatic Calculations on -- Formulas tab, Calculation section | Calculation Options, select Automatic.

If you still have issues, try attaching a sample workbook.

[Afrabat]

Still having problems. It was a colon in the actual formula. I have attached a copy of the workbook. Auto calculations are also on. Not sure why this isn't working correctly. Will someone please see if you can figure it out.

[bgoree09]

Good morning,

In your lookup, the criteria you are searching for needs to be a single cell. Instead of =VLOOKUP(Prodname,Products!$A$2:$B$61,2,FALSE), the formula should start as : =VLOOKUP(B16,Products!$A$2:$B$61,2,FALSE) and drag down. I would also wrap it in an iferror statement so it doesn't throw #REF errors on lines without data. Since there were multiple lookup criteria, the formula in row 17 was returning the price of row 17 in the table, etc.

Best of luck,