VLOOKUP data on another worksheet using dropdown

I've been going round in circles with this for hours and am close to giving up so any help gratefully received!

I have an invoice sheet with a defined table (Product Code, Product, Quantity, Cost Per Unit, Total) and a second sheet called Products with another defined table (Product, Product Code, Cost Per Unit). On the invoice I want to be able to select the Product cell on the invoice which is a drop down showing the various products on the Products sheet. I've managed that using data validation.

Next I want the other cells on the invoice to be populated based on the product I've chosen. I figured this would be with VLOOKUP. So my formula for the Product Code on the invoice is this: =VLOOKUP([@Product],tbl_products,2) but whatever I try I either get #N/A or it pulls in totally the wrong values.

On my products sheet there are gaps because I've used headings for each type of product and there are also characters like ' and " in the Product column (e.g.5'6" Round Table) Could this be my problem?

Wow, quick replies. Thanks.
@Product is the Table cell on the invoice that I want to lookup on the Product sheet table.

The data in the lookup column doesn't have any gaps, the gaps are on the other columns.

I've attached my excel file as I'm struggling to explain it I think! This is what I'm after: http://www.excel-university.com/sele...le-attributes/

Invoice test.xls

That's great, thanks. So you've used INDEX/MATCH rather than VLOOKUP. I wondered whether that was a better option.

I see that it looks up a range of cells rather than the table and when I added more products it automatically picked that up on the invoice which is exactly what I need.

Only question is that it tells me there is an error on the code and cost per unit cells. Any ideas what that would be or should I just ignore it?

It says 'Formula refers to empty cells' even though it has the correct value.

All working fine despite those error messages. Have just ignored them. Thanks very much for your help.