Excel formula help please

_Andy_

Hi

I'm trying to make a spreadsheet at work (mortgage brokers) whereby the user can enter a property value and/or a loan amount to calculate a (rough) idea of likely vaulation and legal costs, if applicable.

In yellow is the cells the user would enter info to, in red, where I want the formula to output to.
Below is an example of the data it would use.

http://i288.photobucket.com/albums/ll191/excelillustration/example.jpg

So, what I'd like the red cell to do is to work out which 'band' the property value or loan amount falls into (first column of data), and to then get the amount it will cost the client from the second column and enter it into the red cell.

Can anyone suggest a formula to achieve this? NB the columns of info will be a lot longer, these are just to illustrate what I'm trying to do.

Hope I've explained this OK.

Thanks in advance for any help

tilamok

Hi

You should use vlookups to do what you want to do.

For cell C2, input formula,
=vlookup(C1+49,999 , $B$7:$D$9, 3)


For cell C2, input formula,
=vlookup(C4+49,999 , $B$12:$D$14, 3)

Hope that's alright

Fonz_2

I like messing about in excel so had a quick crack at this using pretty much what the above post said but with some tidy lookup tables so you can change your tarrifs, etc as time goes on...

If it's useful I'll let you have the excel file to have a play around with.

David

_Andy_

Thanks guys thats awesome. Fonz see PM ,cheers

Fonz_2

Returned your PM