names and drop down lists

[Nick]

I am fairly ignorant of the more complicated aspects of Excel so I hope I can
get help with this problem.
I have a sheet with column C for quantity , column D for items and column E
for the cost. In column D I defined a drop down list with various materials
that we sell. I made each item in the list a Name which referred to a
specific value (e.g. safety glasses refers to $3). I want to create a formula
for each cell in column E which would be the product of column C times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I need to
formula to work on every cell in column E referring to the cells C and D in
the same row.

Thanks,

Nick

 

[T. Valko]

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

 

[Nick]

Biff,
Thanks, that helps, but I don't know how to make that formula automatically
work for E3, E4, etc. I need to generalize the formula to look at the
corresponding C and D cells on any given row.

thanks,

Nick

 

[T. Valko]

Make the references to the lookup table absolute then just drag copy down
the column as needed.

=C2*VLOOKUP(D2,A$2:B$4,2,0)

As you copy down, the cell references will increment accordingly.

 

[Nick]

Biff,
That worked perfectly, many thanks. The copy drag worked like magic.
Now for my education, what does the 2,0 at the end of the formula do?
I take it that the $ sign means that those cells should interact with
whatever cells are chosen for the rest of the formula?

Nick

 

[T. Valko]

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

=C2*VLOOKUP(D2,A$2:B$4,2,0)
=C2*VLOOKUP(D2,A$2:B$4,2,FALSE)

You have a 2 column lookup table. The 2 in the formula is the column number
of the table from which to get the result. VLOOKUP searches the leftmost
column of table for the lookup value D2. You have an option of searching for
an exact match or an approximate match. The 0 (or, you can use FALSE) in the
formula means you want an exact match (case not a factor). I always use 0 to
save a few keystrokes.

The $ signs make the row references to the lookup table absolute. That means
the row references will not change when you copy the formula. Since you're
copying the formula *down* a column you want the row references to the
lookup table to remain constant. If you were copying *across* a row then
you'd want the column references to the lookup table to remain constant:

VLOOKUP(D2,$A2:$B4,2,0)

 

[Nick]

Excellent, thanks

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

=C2*VLOOKUP(D2,A$2:B$4,2,0)
=C2*VLOOKUP(D2,A$2:B$4,2,FALSE)

You have a 2 column lookup table. The 2 in the formula is the column number
of the table from which to get the result. VLOOKUP searches the leftmost
column of table for the lookup value D2. You have an option of searching for
an exact match or an approximate match. The 0 (or, you can use FALSE) in the
formula means you want an exact match (case not a factor). I always use 0 to
save a few keystrokes.

The $ signs make the row references to the lookup table absolute. That means
the row references will not change when you copy the formula. Since you're
copying the formula *down* a column you want the row references to the
lookup table to remain constant. If you were copying *across* a row then
you'd want the column references to the lookup table to remain constant:

VLOOKUP(D2,$A2:$B4,2,0)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Excellent, thanks