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.

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

Originally Posted by Dunc3142

another solution.

This one works as well and it's an intersting approach. I like it, thanks.

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.

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"

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.

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


Regards
SDCh

That is exactly what I did. Thanks!

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

Ray

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

Thanks and keep it up...