Vlookup (or IndexMatch) based on a range of values?

T

thp510

Board Regular
Joined
Oct 19, 2015
Messages
110
Office Version
  1. 365
Platform
  1. Windows
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>I have the following score bands (column C) and their associated categories (column D). I also have a list of scores of several hundred individuals. For example, if there's an individual that scores 351 (cell A3) --they would be in the SMB category since it's between 0-1000 (excluding 1000). If someone scores just 3513 pts (Cell A4), they would be in the ENT category since it's above 3000 but not yet above 5000, which is the score you need to be in the next category, etc. etc--you get the gist. I have hundreds of rows of individual scores that are between 0 and 5,000 and would like to do a Vlookup on the score bands so I can see which category to put them in. Is there a Vlookup function that can tell me which category to put someone in? I'd rather do this than do multiple IF statements. Please help! Thank you in advance.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]SCORES[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]4130[/TD]
[TD]?[/TD]
[TD]SMB[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]351[/TD]
[TD]?[/TD]
[TD]Grow[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]3513[/TD]
[TD]?[/TD]
[TD]MID[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]3[/TD]
[TD]?[/TD]
[TD]ENT[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]351[/TD]
[TD]?[/TD]
[TD]STRAT[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD]511[/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD]331[/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
try this


Excel 2013/2016
ABCD
1SCORESCategory
24130ENTSMB0
3351SMBGrow1000
43513ENTMID2000
53SMBENT3000
6351SMBSTRAT5000
7511SMB
8331SMB
Sheet1
Cell Formulas
RangeFormula
B2=LOOKUP(A2,$D$2:$D$6,$C$2:$C$6)
 
Upvote 0
One way of doing this is to swap your column C and D around so that you lookup the value and read off the category. You can then use =VLOOKUP(A2, $C$2:$D$6, 2, TRUE) to give you what you want. Notice that TRUE is used to give a result if an exact value is not found. This will find the largest value that is less than the value you're looking for if the exact one isn't found.
 
Upvote 0
AlanY said:
try this

Excel 2013/2016
ABCD
SCORESCategory
SMB
Grow
MID
ENT
STRAT

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4130[/TD]
[TD="bgcolor: #E2EFDA"]ENT[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]351[/TD]
[TD="bgcolor: #E2EFDA"]SMB[/TD]

[TD="align: right"]1000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3513[/TD]
[TD="bgcolor: #E2EFDA"]ENT[/TD]

[TD="align: right"]2000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #E2EFDA"]SMB[/TD]

[TD="align: right"]3000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]351[/TD]
[TD="bgcolor: #E2EFDA"]SMB[/TD]

[TD="align: right"]5000[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]511[/TD]
[TD="bgcolor: #E2EFDA"]SMB[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]331[/TD]
[TD="bgcolor: #E2EFDA"]SMB[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=LOOKUP(A2,$D$2:$D$6,$C$2:$C$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

BEAUTIFUL! Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Sum a Row based on found value in a column
Replies
4
Views
730
Eriktoinfinity
E
S
  • Question Question
MACRO to Copy/Delete a Range of Rows depending on Location of Cell Value (Dynamic)?
2 3
Replies
26
Views
8K
skomarni
S

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top