Returning the price of an item

  • Thread starter Thread starter Randell Gower
  • Start date Start date
R

Randell Gower

I need to have Excel return the price of an item when I enter it. I work
with lists and I frequently type in an item and then I manually enter a
price for the item. The cost of the item is known, I just have to look it
up in a book and then manually type in the price.

Is there a way to create a table, using two columns, one with the item
description, one with the price I want returned in my spreadsheet? That way
I make only one entry and the computer returns the proper price for me.

Thanks,
Randy
 
Hi Randy
It sounds like VLOOKUP might do the trick.
Put your data into a table, on a seperate sheet if you
like and then use the VLOOKUP formula to access it.

If your data is in A1, your formula in B1 might look like
this:

=VLOOKUP(A1,Table_Range,2,0)

Regards
Michael
 
Hi Randy!

You want to create a lookup table and then use a lookup
formula.

Create your item/price list in some section of your WS.
Let's say that the ITEM list is in cells C12:C15. The
PRICE list is in cells D12:D15. The cell that you enter
the item for the price lookup is A1. You want the price to
appear in the next cell over which is B1. In B1, enter
this formula:

=VLOOKUP(A1,C12:D15,2,0)

If a match is not found you will get a return of #N/A. If
you do not want to have the #N/A displayed, you can
supress it by changing the formula to:

=IF(ISNA(VLOOKUP(A1,C12:D15,2,0)),"",VLOOKUP
(G12,C12:D15,2,0))

or:

=IF(ISERROR(VLOOKUP(A1,C12:D15,2,0)),"",VLOOKUP
(G12,C12:D15,2,0))

To make things even easier, you can create a dropdown list
of the items and not have to type them in. Just point,
scroll and click. To do that:

Select cell A1. Goto the main menu and select
Data>Validation. In the Allow dropdown box, select List.
In the Source box, enter the item list that you used in
the lookup table, enter =C12:C15. Make sure the In cell
dropdown box is checked. OK out. That's it!

You can use the optional input/error messages if you like.

Good luck!
Biff
 
Back
Top