Excel Spreadsheet Problem

[Abi]

This is a bit of a complicated question, but i've got a screencap to help explain, lol!!

I have to complete some Spreadsheet work for ICT, which is easy enough, but i can't get a formula to work in part of it, to make it work.

The task is based around one company. The spreadsheet records how much the people in one department spend on their phone, and is based around making sure they are on the right network/tariff to save as much money as possible.

This is the part of the spreadsheet i'm having problems with;

http://img166.imageshack.us/img166/7679/exceljd7.jpg (http://imageshack.us)

On the bit which is highlighted, i've set it to work out how much it will cost if they all go onto the same network. It then has underneath this, the one which is the best value for money (That makes it more userfriendly). I've used a MIN formula for it to work out which is the best value for money, but i can't work out how to make it so it also comes up with the name of the network in the gap next to it.

So basically, i need it to have the cheapest cost, and then the name of the network this is on, next to it. So at the minute the total is £4,246.34, which is on W-Mobile, so i need it to come up with W-Mobile next to the total.

I was told it was a VLookUp formula, but whenever i use it, it comes up with an error message. And i'm not even sure if thats the right formula.

So can anyone help me figure out which formula to use, or what i'm doing wrong?

If that didn't make any sense, then shout and i'll try to explain a bit better, lol!

Cheers! :D

[alan45]

There is probably a very simple answer to this one but at this time on a Saturday night its difficult. If no-one can solve it for you tonight I will have a go tomorrow:)

[Abi]

Ok, thanks. And i agree, i bet you anything, that this has the simplest answer ever. I think i've just looked at this spreadsheet too much to think of it, lol!

[alan45]

Ok, thanks. And i agree, i bet you anything, that this has the simplest answer ever. I think i've just looked at this spreadsheet too much to think of it, lol!Close the sheet, pour a glass of vino and tomorrow all will become clear

[Chloe O'brien]

If you are having to predict a value for something then you're probably using the IF function. when using this formula you need to remember that you need to use both open and close brackets and two sets of open and closing quotation marks and commas . Also the correct prediction of value > is greater than < is less than. Am I making any sense. You may have just missed out a comma somewhere.

[Abi]

I dont think the IF function would work, because i would need about 10 of them in one cell for it to do what i want it to.

I just need it to look at the 4 totals above, recognise which is the smallest total, and then enter the name of the company from the cell to the right into the cell the formula is in. I dont think Cell Referencing would work, and this Vlookup just has errors...

But i've taken Alans advice, and got myself a glass of something strong, and hoping someone else can help figure it out! :rotfl:

[Chloe O'brien]

may be if you show a spreadsheet showing the formulas then someone may see what you need to put in the cell.

[Abi]

http://img412.imageshack.us/img412/6967/ictle3.jpg (http://imageshack.us)

This might explain it more.... Hopefully...

[Meh]

try this as the formula:
=VLOOKUP(J26,J20:K23,2,FALSE)

[Abi]

:bow: Thanks! Works perfectly now. I was using =VLOOKUP(J26,J20:K23,k,FALSE) thinking i had to use the actual column reference, rather than 2.

Cheers!