Can entered text produce numbers in another cell?

  • Thread starter Thread starter FlyinPolock
  • Start date Start date
F

FlyinPolock

Hi,

I have a user who wants to be able to type the name of a
book in one cell, and then have the price of the book
automatically populate in another specific cell. There
will be multiple books with different prices. Does anyone
know how to do this?

Thanks in advance!!!
 
use VLOOKUP:


Enter the Books and Prices (perhaps on a second sheet:

A B
1 Moby Dick 23.97
2 Lord Jim 21.56
3 Grisham's Latest 2.98
3 A Bodice Ripper -4.62

then in Sheet1, if the entry is made in A1:

B1: =VLOOKUP(A1,Sheet2!A:B,2,False)
 
Create a lookup table, look in help for Vlookup where you would have the
name of the book
in the leftmost column and the price(s) in the column(s) to the right
 
VLOOKUP will do this, assuming you have the list of titles and their relevant
prices in a table somewhere. Combine that with Data Validation and you can have
a scrollable list of books to choose from via a dropdwon, that once you ahve
made a choice will automatically populate that cell with the book title and the
next cell with the Price.

With your list of Books and prices in any range, named MyList, in any other
cell, eg in a cell that I will refer to as Book_cell

Use Data / Validation / List - Select the list of books.
In the cell next to it, put =VLOOKUP(Book_Cell,MyList,2,0)
 
VLOOKUP will do this, check in Excel help. You'll have a table of book
names/prices elsewhere, and the formula will return the price of the book by
referring to the name of the book and the lookup table.
 
Reason I would use Data Validation is that you stand a real chance of not
entering the book name correctly and therefore not getting a match. DV gets you
past this.
 
Back
Top