Cell Contents Based On Another Cell

November 21, 2010 at 18:10:59
Specs: Windows XP
I basically have no idea if this can even be done but here goes.

In a excel sheet i am trying to have column G equal a value based on a word in Column E

Column E has 100 different possible (suburbs) and each one of these has a different value and i want this to be shown in column G as a number.

For example if E4 has "Colyton" then column G4 =$24.71

or if E4 has "Manly" then G4 = $44.10


See More: Cell Contents Based On Another Cell

Report •


#1
November 22, 2010 at 04:30:28
What you want can be done using a =VLOOKUP() function.

First create a worksheet list of your Suburbs with there related amounts, I have named my sheet Suburbs:

   A          B
1) Colyton    $24.71 
2) Manly      $44.10 
3) Alpha      $10.00 
4) Beta       $11.00 
5) Gamma      $12.00 

continue down 100 rows.

On a new sheet, which I've name Master,
in column A cell 1 I put the heading: Suburb Name
in column B cell 1 I put the heading: Amount

In column B cell 2 enter the formula:

=IF(ISBLANK(A2),"",VLOOKUP(A2,Suburbs!$A$1:$B$100,2,))

When you enter a suburb name into cell A2,
the formula will lookup that name in the Suburbs sheet,
and return the amount indicated.

You can take the formula and drag it down as many rows as necessary.

MIKE

http://www.skeptic.com/


Report •

#2
November 22, 2010 at 14:03:31
thanks heaps this worked well

Report •

#3
November 22, 2010 at 17:34:40
Glad I could help.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question