CODEWITHSUNDEEP
excelif-statementexcel-formulavlookupintervals
Alytas
Alytas

Reputation: 61

Filling columns based on category and intervals

I have the following problem. I need to fill in the points column based on the Category and score interval. I was trying to do it through the IF function but it seems very complicated. Everytime I try it with the HLOOKUP it also does not seems to work, as I simply don't know how to implement the category selection and interval selection in the formula. Any help would be appreciated!

enter image description here

Upvotes: 0

Views: 81

Answers (2)

pnuts
pnuts

Reputation: 59460

Try in C2 and copied down to suit:

=2*IF(A2="A",MATCH(B2,{0,26,101}),IF(A2="B",MATCH(B2,{0,100,200}),IF(A2="C",MATCH(B2,{0,40,150}))))

Upvotes: 1

M. Wise
M. Wise

Reputation: 186

You need a lookup row in each group with the low end of the interval to make the HLOOKUP work:

enter image description here

Upvotes: 0

Related Questions