Complex Vlookup or simular based on less than, equal to or more than

[thedeadzeds]

Hi Guys,

Is there a way to craete a vlookup or similar to achive what i am currenly manually typing in, in the 'Point' column below.

Ideally i want a formula to be able to look at the 'Score answer' in the Result table and input the correct Point based on the Score in the Matrix table depending on the Section and Question.

For example, in the result table, for Section 3 (row 3), Question New the Score answer is 92.11. I then look down the whole Matrix table, find section 3 , find Used in the question Column, see that the Score is more than 92 so the Point column in the Result table would show 2.

Hope this makes sense.

Thanks


Result Table

Section	Question	Score Answer	Point
Section 1	New	74	0
Section 1	New	91.58	1
Section 3	Used	92.11	2
Section 3	Used	92.99	2
Section 1	New	96.7	6
Section 2	New	97.77	7

Scoring Maxtrix Table

Section	Question	Score	Points
Section 1	New	<91	0
Section 1	New	>=91	1
Section 1	New	>=92	2
Section 1	New	>=93	3
Section 1	New	>=94	4
Section 1	New	>=95	5
Section 1	New	>=96	6
Section 1	New	>=97	7
Section 1	New	>=98	8
Section 1	New	>=99	9
Section 1	New	>=100	10
Section 1	Used	<91	0
Section 1	Used	>=91	1
Section 1	Used	>=92	2
Section 1	Used	>=93	3
Section 1	Used	>=94	4
Section 1	Used	>=95	5
Section 1	Used	>=96	6
Section 1	Used	>=97	7
Section 1	Used	>=98	8
Section 1	Used	>=99	9
Section 1	Used	>=100	10
Section 2	New	<91	0
Section 2	New	>=91	1
Section 2	New	>=92	2
Section 2	New	>=93	3
Section 2	New	>=94	4
Section 2	New	>=95	5
Section 2	New	>=96	6
Section 2	New	>=97	7
Section 2	New	>=98	8
Section 2	New	>=99	9
Section 2	New	>=100	10
Section 3	Used	<91	0
Section 3	Used	>=91	1
Section 3	Used	>=92	2
Section 3	Used	>=93	3
Section 3	Used	>=94	4
Section 3	Used	>=95	5
Section 3	Used	>=96	6
Section 3	Used	>=97	7
Section 3	Used	>=98	8
Section 3	Used	>=99	9
Section 3	Used	>=100	10

 

[jasonb75]

Easiest way, I've only shown the top part of the scoring matrix table for reference in the formulas.

Book1
	A	B	C	D
1	Result Table
2	Section	Question	Score Answer	Point
3	Section 1	New	74	0
4	Section 1	New	91.58	1
5	Section 3	Used	92.11	2
6	Section 3	Used	92.99	2
7	Section 1	New	96.7	6
8	Section 2	New	97.77	7
9
10
11	Scoring Maxtrix Table
12	Section	Question	Score	Points
13	Section 1	New	0	0
14	Section 1	New	91	1
15	Section 1	New	92	2
16	Section 1	New	93	3
17	Section 1	New	94	4
Sheet1

Cell Formulas
Range	Formula
D3:D8	D3	=MAXIFS($D$13:$D$56,$A$13:$A$56,A3,$B$13:$B$56,B3,$C$13:$C$56,"<="&C3)

 

[thedeadzeds]

Amazing, thank you so much