# LOOK UP Formula? I need to return a score depending on the value of another cell.

1. ## LOOK UP Formula? I need to return a score depending on the value of another cell.

I have an Excel spreadsheet with five columns, the number of rows can be infinite after the top row which consists of . Column A=Hours, B=Score, C=X, D=Y and E=Z. I would like to be able to enter hours into column A and have ther score of X,Y, Z or a dash (-) self populate. If the number of hours is less than 20 the score should be a dash, between 20-30 the score would be X, 30-40 =Y, over 40=Z. It seems to me that this shouldn't as much trouble as I'm having but I just can't figure it out. Any help would be greatly appreciated.

2. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

hi RMMARTY. wow took you 5 years for your 1st post?

not sure what you want with column C:E. but attaching a rough eg. note that your scores clashes. 20-30 is "X". 30-40 is "Y". so is 30 X or Y? adjust the formula in red accordingly. these numbers should be the minimum to get the scores. meaning 0 is the minimum to get "-", 20 to get "X", and so on
=LOOKUP(A2,{0,20,30,40},{"-","X","Y","Z"})

3. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

another solution.

4. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

Originally Posted by benishiryo
hi RMMARTY. wow took you 5 years for your 1st post?

not sure what you want with column C:E. but attaching a rough eg. note that your scores clashes. 20-30 is "X". 30-40 is "Y". so is 30 X or Y? adjust the formula in red accordingly. these numbers should be the minimum to get the scores. meaning 0 is the minimum to get "-", 20 to get "X", and so on
=LOOKUP(A2,{0,20,30,40},{"-","X","Y","Z"})
Thank you for the reply, this works

5. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

Originally Posted by Dunc3142
another solution.
This one works as well and it's an intersting approach. I like it, thanks.

6. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

Thank you both for your replies, very helpful. But now what if we throw in a kink. If we added a rating column and A, B, or C will be the value. Depending on the rating the scores for X, Y, Z will vary. For instance the value of X would be 30-40 for A, 25-35 for B and 20-30 for C. The scores for Y and Z would follow in the same manner.

7. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

I'm not sure want you are looking for, but I'll try with 2 solution (see attachment).

Actually I'm confused with the scores because it has intersection between score i.e 30-40 for A and 25-35 for B.
So if the value is between 30-35 which will come up? "A" or "B"

8. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

Yes, dependant upon the rating. I just solved it by having a table feed the XYZ cells according to the rating. Then the score is determined by where the hours fall with in those cells and returns the XY or Z. Far more complicated then I had hoped and I'm betting theres an an equally complicated formula that will do the same thing. But by having the XYZ column fed from seperate table I think it will be easier to update if those numbers change. Confused? When I get back to work I'll see about attaching a copy of what I did. Thank you everyone and have a great weekend.

9. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

Add another solution: range value X, Y and Z on another sheet

Regards
SDCh

10. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

That is exactly what I did. Thanks!

11. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

Thanks to everyone that replied. I did get this figured out and everyones help is greatly appreciated.

Ray

12. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

Brilliant formula dear Dunc... by your formula I solved another problem of me based on same steps

Thanks and keep it up...

There are currently 1 users browsing this thread. (0 members and 1 guests)