• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find or match a number within a range and return a value in a another row


New Member
I have a score between 1 and 100. Eg, 80. I then want to find that number in a column and highlight it. However, it doesn't need to match the number perfectly. Eg, it could be within a range of -10 or plus 10 eg, between 70 and 90.
So I have a few ranges.
Eg in the worksheet attached. There are three calculations I need to make.
1. Column E to Col H is the example above. Eg, E = 30. F = 75. G = 75. H = 45. This will return the text in col B called Customer service.

2. Col J to Col P - Matches and returns the top 2 or three values between .5 and 3. Eg, going back to the example immediately above. J = 1. N = 1. O = 1. Since the scores are greater than zero and J = True. N = True. O = True it returns the value of "Customer Service"
It would be great if I could put in the values and the formula calculates the above. Eg, the 7 values taken from the assessment would be:
Aes = 30. Eco = 51. Ind = 40. Pol = 50. Alt = 60. REg = 70. The = 45. The top three values are col J, N and O. and would therefore return the value "Customer service."

3. Three ranges. Eg,
Q to R
U to V
Y to Z
I have numbers between 1 and 10 if they fall into the ranges above then they return a Customer service.. Eg,
Empathy = 8.5
Practical = 7.2
Systems = 6.7
Then this would return the value "Customer service."

I hope this makes sense. Thank you very much for your assistance in advance. Excel is not a super power of mine.