IF Formula

[Bankesy]

Hello

 

Does anybody know if the following is possible:

 

Teachers would like to be able to set a level in Excel based on the result of another column.

 

For example if any of the cells C2 to C10 contain a value of 5-10 or 11-15 and so on then the cells D2 to D10 appear with the level that mark relates to.

 

Any ideas welcome.

[LosOjos]

It is possible with an 'IF', but can become convoluted very quickly. A better/more expandable way IMO would be an INDEX/MATCH - I've attached an example for you

Index-Match-Example.xls

[Seb1780]

Or use a LOOKUP to return the level based on the value - we use this to convert APS points to grades.

[hallb15]

It is possible with an 'IF', but can become convoluted very quickly. A better/more expandable way IMO would be an INDEX/MATCH - I've attached an example for you

^

This is how I would do it too.

You might want to consider protecting the cells so teachers can enter marks, but not edit/mess up the cells containing the formula!

[LosOjos]

Or use a LOOKUP to return the level based on the value - we use this to convert APS points to grades.

 

I used to do that, but it became problematic when we started getting larger ranges, you'd end up with huge lists of values covering every possibility - the INDEX/MATCH was a lifesaver when I figured it out, you simply enter boundaries and it returns the level

[Seb1780]

I used to do that, but it became problematic when we started getting larger ranges, you'd end up with huge lists of values covering every possibility - the INDEX/MATCH was a lifesaver when I figured it out, you simply enter boundaries and it returns the level

 

If the LOOKUP is set as a range lookup (last parameter = TRUE) then you only need the boundaries in your lookup table.

[featured_spectre]

It is possible with an 'IF', but can become convoluted very quickly. A better/more expandable way IMO would be an INDEX/MATCH - I've attached an example for you

 

Trying to expand this so it has 29 entries, which are both text, and it really doesnt like it

[LosOjos]

Trying to expand this so it has 29 entries, which are both text, and it really doesnt like it

 

They have to be in ascending order, and both the Index and Match have to cover the same range

 

- - - Updated - - -

 

If the LOOKUP is set as a range lookup (last parameter = TRUE) then you only need the boundaries in your lookup table.

 

Doesn't this round to nearest though?

EDIT: No, you're quite right! One of the great (but sometimes frustrating) things about Excel is there's usually more than one way to do something

Edited October 24, 2014 by LosOjos

[featured_spectre]

Yeah I expanded the index and match to cover the cells. Will try again when I've had something to eat

[featured_spectre]

ok, I can't get it to return the proper text value, it keeps giving me a name from the same column rather than the corresponding row next to it.

@LosOjos

 

Basically need it to say in Column A - Name 1:Name 29, Column B - Company 1:Company 29...however the formula returns a random name from column A instead of the company in Column B

[Seb1780]

@nephilim - you do have your table of lookup values separate from the area you are trying to populate don't you?

 

Also, the reference to the table need to be absolute or named range references, otherwise it fails when you copy it.

Edited October 24, 2014 by Seb1780

[featured_spectre]

yes. The table of lookup values is elsewhere on the sheet (column J and column K)

[Seb1780]

Try this in B2

 

=vlookup(a1,$j$1.$k$29,2,false)

 

This will use the value in a1 to lookup from the table in j1.k29 and return the value in the second column. The false parameter will return only full matches.

[featured_spectre]

Gives me a #NA! on the cell where the company needs to populate. IF I could use access I'd have been done by now, but this office refuses to buy it

[LosOjos]

Gives me a #NA! on the cell where the company needs to populate. IF I could use access I'd have been done by now, but this office refuses to buy it

 

Post an example @nephilim and I'll take a look what's wrong - the formula definitely works, large portions of my data analysis rely on it

[featured_spectre]

If it was Access would be a case of

 

IF input A = X then input B = Y...but it just does not like it...no matter which way I try

[LosOjos]

If it was Access would be a case of

 

IF input A = X then input B = Y...but it just does not like it...no matter which way I try

 

Post an example @nephilim and I'll take a look what's wrong - the formula definitely works, large portions of my data analysis rely on it

[featured_spectre]

ok...in this sample... Name in Cell A2 would be Joe Bloggs. In Cell B2 it needs to auto populate with Joe Bloggs Inc as referenced in the column J2 and K2.

 

Using the formula -

=VLOOKUP(A2,$J$2:$K$29,2,FALSE)

I get #N/A

 

 

Using the formula -

=INDEX($J$2:$J$29,MATCH($A2,$K$2:$K$29,1))

I get the name from J29.

 

Not entirely sure what is going wrong.

[LosOjos]

Using the formula -

=INDEX($J$2:$J$29,MATCH($A2,$K$2:$K$29,1))

I get the name from J29.

 

Not entirely sure what is going wrong.

 

The last argument of MATCH (1 in this case) is the match type - 1 is less than, 0 is exact, -1 is more than. If you're matching strings, you'll want an exact match, i.e. change it to this:

 

=INDEX($J$2:$J$29,MATCH($A2,$K$2:$K$29,0))

[featured_spectre]

That fixed it, thank you

[LosOjos]

That fixed it, thank you

 

HTH

 

For the VLOOKUP version, the problem is likely similar - the last argument, TRUE/FALSE, refers to whether it should be an approximate match or not

[Seb1780]

I should have refreshed before replying Edited October 24, 2014 by Seb1780
Ninja'd