Return Lookup based on range cell

Josh88

New Member
Joined
Sep 7, 2017
Messages
15
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:
DE
DaysMargin
11.4
21.3
3-71.2
8-141.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?
2ABC
3Number of days5?

<tbody>
</tbody>



Thanks!!
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,753
Office Version
2007
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Days</td><td style="text-align: center;;">Margin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1.4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1.4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">1.3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1.3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">1.2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1.2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">1.2</td><td style="text-align: center;;">8</td><td style="text-align: center;;">1.15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">1.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">1.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">1.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">1.15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;">1.15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;">1.15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Rawdata</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">15,6,$E$2:$E$5/(<font color="Red">$D$2:$D$5<=B2</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Josh88

New Member
Joined
Sep 7, 2017
Messages
15
I suggest you put the table of days and margin as follows.
Put the vlookup formula in cell C3

ABCDEF
1 DaysMargin
2 111.4
3Number of days51.2221.3
4 371.2
5 8141.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>

CellFormula
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

Active Member
Joined
Mar 5, 2018
Messages
296
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:

ABCDE
1DaysMargin
211.4
3Number of days51.221.3
431.2
581.15
6151.14

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

Worksheet Formulas
CellFormula
C3=VLOOKUP(B3,D2:E6,2,TRUE)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,753
Office Version
2007
Platform
Windows
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))
 

Forum statistics

Threads
1,077,828
Messages
5,336,625
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top