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

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
361
Office Version
  1. 365
Platform
  1. Windows
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
SectionQuestionScore AnswerPoint
Section 1New740
Section 1New91.581
Section 3Used92.112
Section 3Used92.992
Section 1New96.76
Section 2New97.777


Scoring Maxtrix Table
SectionQuestionScorePoints
Section 1New<910
Section 1New>=911
Section 1New>=922
Section 1New>=933
Section 1New>=944
Section 1New>=955
Section 1New>=966
Section 1New>=977
Section 1New>=988
Section 1New>=999
Section 1New>=10010
Section 1Used<910
Section 1Used>=911
Section 1Used>=922
Section 1Used>=933
Section 1Used>=944
Section 1Used>=955
Section 1Used>=966
Section 1Used>=977
Section 1Used>=988
Section 1Used>=999
Section 1Used>=10010
Section 2New<910
Section 2New>=911
Section 2New>=922
Section 2New>=933
Section 2New>=944
Section 2New>=955
Section 2New>=966
Section 2New>=977
Section 2New>=988
Section 2New>=999
Section 2New>=10010
Section 3Used<910
Section 3Used>=911
Section 3Used>=922
Section 3Used>=933
Section 3Used>=944
Section 3Used>=955
Section 3Used>=966
Section 3Used>=977
Section 3Used>=988
Section 3Used>=999
Section 3Used>=10010
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,475
Office Version
  1. 365
Platform
  1. Windows
Easiest way, I've only shown the top part of the scoring matrix table for reference in the formulas.
Book1
ABCD
1Result Table
2SectionQuestionScore AnswerPoint
3Section 1New740
4Section 1New91.581
5Section 3Used92.112
6Section 3Used92.992
7Section 1New96.76
8Section 2New97.777
9
10
11Scoring Maxtrix Table
12SectionQuestionScorePoints
13Section 1New00
14Section 1New911
15Section 1New922
16Section 1New933
17Section 1New944
Sheet1
Cell Formulas
RangeFormula
D3:D8D3=MAXIFS($D$13:$D$56,$A$13:$A$56,A3,$B$13:$B$56,B3,$C$13:$C$56,"<="&C3)
 
Solution

Forum statistics

Threads
1,147,559
Messages
5,741,817
Members
423,689
Latest member
Jords998

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top