Please Help, I am desperate! EXCEL FORMULA?

Question

I have to use a lookup function for the following and I can't for the life of me figure it out.
Data:
Freshman 0 to <32 credits
Sophmore 32 to <63 credits
Junior 63 to < 94
Senior 94 or more credits.

Basically, I was given a spreadsheet and have to fill in all the columns using formulas and this is the only one I cant get. Credits start in E5, for example E5 contains 57 E6 contains 94 and so forth... i need to come up with a formula that will allow in G5, Sophmore to come up without typing it in, so if the credit count changes everything else will also automatically. I hope some kind soul can help me. This is proberly so easy, I am just so tired , been at it for 4 hours doing the whole spreadsheet. thanks in advance

Answer 1

I accept the challenge for the fun of it.

...will get back to you.


OK. in cell G5, based on what you describe, the formula is "=VLOOKUP(+E5,$B$2:$C$5,2)" Note the dollar signs in the formula referring to cells B2 and C5. This will allow you to copy cell G5 into G6, G7, G8, and so on if you have more values in column E.


NOTE: In column B2 to B5 are the numbers 0, 32, 63, and 94 respectively. In column C2 to C5 are the words Freshman, Sophomore, Junior, and Senior respectively.

Answer 2

1

Answer 3

John,
I built my lookup table on a worksheet called 'Lookup table'.
Column a was 0 through 94 and columen B was Freshman Through Senior at the apprioate number value.

On the main worksheet, D had the value that I wanted to check against the table. Using the following formula:
=VLOOKUP(D2,'Lookup table'!A$1:B$95,2)

The valume in D2 was checked against the lookup table in column A, Once it found the number, It went to the next column(2) and placed that value in the cell that the formula was written.

If the lookup value is greater than 94 it will use the last value on the table, which is Senior.

O Cacador is shorter and sweeter, but you said you wanted to use 94 entries.

Answer 4

I don't quite remember but I've had to do almost the exact same thing before.

In the function (fx=) bar type this:

=IF(E5>32, "freshman")

that takes care of one part, but I'm not sure how to do the rest... try googling "excel tutorial, lookup function" or something.

good luck!

Answer 5

Suppose your credit is in cell a1 and you want g5 to show freshman if 15 is entered.

in cell g5
=if(a1<32, "Freshman", if(a1>31 and a1<63, "Sophmore", if(a1>62 and a1<94, "Junior", if(a1>93, "Senior",""),"","","")


It probably won't work if you cut and paste it, I didn't check, but the basic idea is

if( check is true, do this, otherwise do this)

and on the false part of it put another if check

Answer 6

Create a little array; off to the side or on another worksheet in the file. (I put it in Sheet2 for your example)

0......Freshman
32.....Sophomore
63.....Junior
94.....Senior

Now, in G5 put this formula: =VLOOKUP(E5, Sheet2!A1:B4, 2)

Answer 7

Try conditional formating

Answer 8

I think @vlookup is the answer but I don't know the syntax.

Answer 9

=IF(A1<32,"freshman",(IF(A1<63, "sophomore", IF(A1<94, "junior", "senior"))))