Lookup function

K

Kepf

I am very new to the lookup function, so if there is a better way than
LKUP to accomplish this, let me know.

I have an excel file containing my item number in one row (column?),
and how much it costs for the item in the next row (column?) over.

What I want to do is "marry" these somehow so that on another excel
sheet it will automatically tell me what the item cost is based on the
item number.

What steps would I take to go about doing this, or is it even
possible?

Thanks,
- Excel Novice
 
P

Pete

Assume your item numbers are in cells A1 to A20 on Sheet1, and prices
are in B1 to B20. In a separate sheet you can use A1 to type in the
item number, and in B2 you would need this formula:

=VLOOKUP(A1,Sheet1!A1:B20,2,0)

If the item number you enter into A1 does not exist in your table, this
will return #NA, otherwise it will return the appropriate price.

Hope this helps.

Pete
 
K

Kepf

thank you for such a quick reply.

But just so I learn to do it myself and not simply "copy," in the line
you quoted to me above...:

=VLOOKUP(A1,Sheet1!A1:B20,2,0)


...what do each of these represent? I think I know a few, but if I am
wrong please tell me.

A1
Sheet1 - name of sheet being "looked up"
A1
B20
2
0


Thanks again for your patience with my "novice-ness."
 
P

Pete

We were all novices once - you will quickly learn to distinguish "row"
and "column" properly.

The first A1 refers to the cell in the second sheet into which you type
the item number you are interested in.

The second A1 is part of the range A1:B20 in Sheet1, and this refers to
the block of cells starting with A1 and going to B20 (i.e. 2 columns
wide and 20 rows deep). I have assumed that your item numbers and
prices occupy this block of cells, so if it was longer (say, 50 rows)
you would refer to this as A1:B50. Two or more columns like this are
refered to as a table - this is your lookup table.

The number 2 indicates which column in the table the value should be
returned from. You may have wider tables with more columns, so changing
this parameter would enable you to fetch data from a different place in
another situation.

The 0 at the end means that you want an exact match.

Hope this helps your understanding.

Pete
 
Top