Is there a way to return records?

R

rayteach

I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15 records.
I want to know if there is a way to (for example) enter the product code of
an item in a cell in another worksheet (or a different workbook) and have the
complete record of that item be duplicated.
ray
 
K

Ken Wright

Take a look at VLOOKUP and use 5 of these in 5 consecutive cells, with each
one referencing the same cell (The first of the 5 which you will put the
Product code in) and returning the next piece of data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
D

Duke Carey

Name the range that contains your product codes by selecting all the cells,
then using Insert>Names>Define and typing in a brief name - let's call it
Products

Now, in the cells adjacent to the cell where you'll input the product code
use (assuming the product code is entered in A1)

=VLOOKUP($A$1, Products, 2, 0)

The 2 instructs Excel to get whatever is in the second column of the product
table, so change this number to 3, 4, etc as you copy it into other cells.
The 0 requires an exact match on product code and returns an #NA error if
there is no match
 
R

rayteach

Your formula worked perfectly but, perhaps I am being thick headed, I still
need to copy the formula through each cell. I do not see how this is an
advance over copy and paste? There is no way to simply type the product code
into a cell and then have the rest of the record copied into the adjacent
cells?
 
R

rayteach

Thank you for your prompt response. The post by Duke Carey was more help to
me as he guided me through the vlookup process in a way a beginner like me
can understand. I have also repsonded to his post as it did not automate the
process the way I am hoping.
 
B

Biff

I do not see how this is an
advance over copy and paste?
Click to expand...

Using copy/paste, you'd have to do this every time you want to lookup some
data. Using the lookup formulas you do it once by just changing the lookup
value.

Also, the examples you've been given are rather basic. They can be modified
to give them much more capability.

For example, you don't need to enter 3 different formulas, one for each
column index number:

=VLOOKUP($A$1, Products, 2, 0)
=VLOOKUP($A$1, Products, 3, 0)
=VLOOKUP($A$1, Products, 4, 0)

You can write one formula and as you copy it across to other cells, have the
column index number automatically increment:

=VLOOKUP($A$1, Products, COLUMNS($A:B), 0)

Biff
 
R

rayteach

Thank you so much for your response. That makes it much easier to use the
VLOOKUP function.
 
Top