MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can someone help me with this please?


Posted by Dug on February 14, 2002 6:39 PM

How do i name a table so i can use the name of the table in my index fomula? I want to have one column with items and the column to the right of it have prices so i can choose the item from a drop down menu and have the price go into a certain cell that my pricing formula can reference.
Thanks for any help you can give.


Posted by Dreamboat on February 14, 2002 7:05 PM

You probably just need to use a VLOOKUP formula here.

Suppose item name is in column A and price in column B of sheet 2. Select columns A and B and hit Insert-Name-Define. Call it MyData (or something).

In sheet 1, you type the item name into C3. In D3, you type:

=vlookup(c3,mydata,2,1)

where: c3 is the item name (part number, etc.)
where: mydata is your *database*
where: 2 is the number of the column (in the *database*) that contains the value you want to return
where: 1 means don't find the nearest value, only the exact value

To create the dropdown on sheet 1, select only the product names on sheet 2, hit Insert-Name-Define and call it something like Products. Then click on cell C3 (continuing with my example above), hit Data-Validation, choose List from the dropdown. In the Source box, type =Products
You can use the Insert-Name-Define to name the range for your index formula as well, but vlookup should be much easier if you can use it.

Posted by Aladin Akyurek on February 14, 2002 11:37 PM

Exact match requires 0 (that's, FALSE). [NT]