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:

D	E
Days	Margin
1	1.4
2	1.3
3-7	1.2
8-14	1.15

<tbody>
</tbody>

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?

2	A	B	C
3	Number of days	5	?

<tbody>
</tbody>

​

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
1				Days	Margin
2				1	1	1.4
3	Number of days	5	1.2	2	2	1.3
4				3	7	1.2
5				8	14	1.15

<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>
</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
1				Days	Margin
2				1	1.4
3	Number of days	5	1.2	2	1.3
4				3	1.2
5				8	1.15
6				15	1.14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C3 =VLOOKUP(B3,D2:E6,2,TRUE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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))