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

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: AmountIn 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

thanks heaps this worked well

Ask Your Question

Weekly Poll