Lookup Function:

[dyowell]

I have a small database of customers and products. I am
using this database to compose quotes and invoices. I
have quite a few products and would like to be able to
enter the product number and have the description and unit
price entered automatically.

I have never done this before and have searched the
internet on how to do this, but have come up empty. I was
wondering if someone could explain to me (in pretty basic
terms) how to go about doing this. It seems as though I
may be making this more difficult than it is. Thanks for
the help in advance.

P.S. I would also be willing to email a small sample file
to someone if they would think that this would make things
easier.

 

[Don Guillett]

Look here for some ideas.
http://www.contextures.com/tiptech.html

 

[Gord Dibben]

dyowell

VLOOKUP may be what you are looking for.

Assume you have 4 columns

A is Product Number
B is Product Name
C is Description
D is Price

Range is A250

Insert>Name>Define this range as "mytable"

In F2 enter =VLOOKUP(E2,mytable,2,FALSE) to return Product name

In G2 enter =VLOOKUP(E2,mytable,3,FALSE) to return Description

In H2 enter =VLOOKUP(E2,mytable,4,FALSE) to return Price

In E2 enter a Product Number to see the results.

These are the basics.

Can be done across 2 sheets and you can use Data Validation>List to create a
drop-down list in E2 for selection of your number.

Excel Help has quite a bit of info and some examples.

Gord Dibben Excel MVP

 

[dyowell]

I tried to enter these formulas as you have explained,
however, the formulas continue to be the only thing that
shows up in the description and unit price cells.

What am I doing wrong. Thanks for the help in advance.

 

[Aladin Akyurek]

Your formula cells are text-formatted. Try the following:

Select formula cells.
Run Edit|Find.
Set Find What to:

=

Set Replace With to:

=