Multiple IF Statement

[certain_death]

Hey guys

Can anyone help me please!

I need help with an IF formula...what I want to do is this:

if the value in cell A1 = 0 to 30 put "0-30" (as text), if the value in cell A1 = 31 to 60 put "31-60" (as text), if the value in cell A1 = 61 to 90 put "61-90" (as text), or if it is none of these put "91-120" (as text)

Would be really grateful if anyone could help please

Thanks
Mark

[daddylonglegs]

Hello Mark,

Please see forum rules about thread titles and alter yours accordingly, thanks

[daddylonglegs]

Thanks for changing the thread title, Mark......

Here's one way....

=LOOKUP(A1,{0,31,61,91;"0-30","31-60","61-90","91-120"})

[certain_death]

Ah thats great ...

Perfect

Thanks for your help

Nice one

[Rickster1978]

This is very similar to something I need to resolve at the moment. I have a cell that could contain 1 of 8 team names. I want to bring in the squad list for each team if the team name is displayed.

The squad lists are contained on a seperate worksheet and I cant seem to get the formula to link to the worksheet. I've looked at certain_deaths formula and edited it slightly:

=LOOKUP(B14,{"Junior Bulls Athletic","Ragass Rovers","Brazilliant","Un-Nameable FC";"0-30","31-60","61-90","91-120"})

What I want to do though is replace "0-30" with information contained in a cell of another worksheet. I keep getting an error message when I try to do this. Also would I then have to repeat the formula for each of the different players or is there an easier way of pulling in all of the names.

Many thanks in advance, Ricky

[Rickster1978]

I'm still ripping my hair out on this one guys. any suggestions at all?

[NBVC]

The list in the first Lookup array must be in alphabetically ascending order...

re-arrange the list and its corresponding values in the second list...

[jwhitwell]

Probably need something other than LOOKUP in your case (I think); something like:

=VLOOKUP(B14,Sheet2!A1:B8,2,FALSE)

B14 would be where you enter your team name.

Sheet2 would be the other sheet where your squad names are.

Range A1:B8 would be where the lookup takes place - A1 to A8 would be your list of 8 team names, B1 to B8 would be the squad names you want returned based on the team name you enter in B14 on the other sheet.

Does that help?

[Rickster1978]

many thanks Jwhitwell, I think I'm awfully close to getting it sorted now. Just one quick question.

I've got the vlookup to locate the team and then display whatever is in the next cell. If I enter the squad list in this cell I get a long string of text eg: "Mark, John, Fred, Dave" ideally I could do with it displaying the information in a list like

Mark
John
Fred
Dave

Is this easy enough to do. Sorry if I sound extremely dim but I'm still fairly new to spreadsheets.