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

[thp510]

<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]

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D
1	SCORES		Category
2	4130	ENT	SMB	0
3	351	SMB	Grow	1000
4	3513	ENT	MID	2000
5	3	SMB	ENT	3000
6	351	SMB	STRAT	5000
7	511	SMB
8	331	SMB
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,$D$2:$D$6,$C$2:$C$6)

 

[gsbelbin]

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.

 

[thp510]

try this

Excel 2013/2016

	A	B	C	D
SCORES	Category
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]

BEAUTIFUL! Thanks!

 

[thp510]

Ahh, didn't know that. Perfect too! Thank you!

 

[AlanY]

You’re welcome