Return Lookup based on range cell

[Josh88]

Hi all.

I'm renting out construction tools and have my margin at different rates depending on the number of rental days.
This is what my days-margin cells look like:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]Days[/TD]
[TD="align: center"]Margin[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1.4[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1.3[/TD]
[/TR]
[TR]
[TD="align: center"]3-7[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]8-14[/TD]
[TD="align: center"]1.15[/TD]
[/TR]
</tbody>[/TABLE]
If I have A3 titled "Number of Days", B3 shows the number (i.e 5) and want C3 to show the appropriate margin, what fomula do I need for C3?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Number of days[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]?[/TD]
[/TR]
</tbody>[/TABLE]

​

Thanks!!

 

[DanteAmor]

I suggest you put the table of days and margin as follows.
Put the vlookup formula in cell C3

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:157.78px;" /><col style="width:76.04px;" /><col style="width:115.01px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td colspan="2" style="background-color:#ffff00; text-align:center; ">Days</td><td style="background-color:#ffff00; ">Margin</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1.4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Number of days</td><td style="text-align:right; ">5</td><td style="text-align:right; ">1.2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">1.3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">7</td><td style="text-align:right; ">1.2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td style="text-align:right; ">8</td><td style="text-align:right; ">14</td><td style="text-align:right; ">1.15</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C3</td><td >=VLOOKUP(B3,$D$2:$F$5,3,1)</td></tr></table></td></tr></table>

 

[Fluff]

How about

Book1
	A	B	C	D	E
1				Days	Margin
2		1	1.4	1	1.4
3		2	1.3	2	1.3
4		3	1.2	3	1.2
5		4	1.2	8	1.15
6		5	1.2
7		6	1.2
8		7	1.2
9		8	1.15
10		9	1.15
11		10	1.15
Rawdata

Cell Formulas
Range	Formula
C2	=AGGREGATE(15,6,$E$2:$E$5/($D$2:$D$5<=B2),1)

 

[Josh88]

I suggest you put the table of days and margin as follows.
Put the vlookup formula in cell C3

	A	B	C	D	E	F
Number of days

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:157.78px;"><col style="width:76.04px;"><col style="width:115.01px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , colspan: 2, align: center"]Days[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Margin[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1.2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1.15[/TD]

</tbody>

Cell Formula C3 =VLOOKUP(B3,$D$2:$F$5,3,1) <tbody> </tbody>

<tbody>
</tbody>

In the Formula, can you please explain what the 3,1 represent?

 

[Toadstool]

You don't specify what to do if the number of days exceeds 14 so I'll assume they get one more discount point.

The 3-7 and 8-14 are tricky to decode so you can just use the bottom end of the range which means the ascending order allows a VLOOKUP with approximate match:

	A	B	C	D	E
Number of days

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

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1.4[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1.2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1.3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1.2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1.15[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]1.14[/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] "]C3[/TH]
[TD="align: left"]=VLOOKUP(B3,D2:E6,2,TRUE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

In the Formula, can you please explain what the 3,1 represent?

Hi @Josh88, I explain the operation of the formula.

=VLOOKUP(B3,$D$2:$F$5,3,1)

The range D2:F5 has 3 columns: D is column 1, E is column 2, F is column 3.
The formula looks for the value of cell B3 in column 1 (Column D).
If find the data then get the value from column 3 (Column F)
The 1 means to find the closest match in column 1. (The data in column 1 (Column D) must be sorted in ascending order))