Finding a value in a specific row

[Whiox]

Dear MrExcel community

Question
How can I look up certain values in a table depending on a variable please?

Background
I have a table that is populated with numbers. The first column is a running date column, with each row being another day.
I am looking for a formula that allows me to look up values that are in a particular row of interest (e.g. the value in column C).
The row of interest depends on a time variable that can change. The variable being any number of months.

Example
Let's say my time variable is "3 months". I'd then like to look up the value in column C that is in the row that corresponds to exactly 3 months after the start date (the start date is in cell A1). How can I do that please?

Many thanks

 

[levy77]

Hi Whiox,

Check the sheet I have attached. The formula I used is;
=INDEX($B$2:$F$366,MATCH(DATE(YEAR($A$2)+$I$2,MONTH($A$2)+$J$2,DAY($A$2)+$K$2),$A$2:$A$366),MATCH($I$3,$B$1:$F$1))
Select the years, months and days you would like as your time variable and which column you would like to search and the output returns the value you want.

Cheers

 

[DanteAmor]

Check the following example, only capture in cell B3 the number of months and in D3 the number of column B = 2, C = 3, etc.
Put the formulas as indicated in each cell.


<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:86.5px;" /><col style="width:76.99px;" /><col style="width:89.35px;" /><col style="width:82.69px;" /><col style="width:95.05px;" /></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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">START DATE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">MONTHS</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">SEARCH FOR</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">COLUMN</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">08/06/2019</td><td style="text-align:right; ">3</td><td style="background-color:#ffc000; text-align:right; ">08/09/2019</td><td style="text-align:right; ">3</td><td style="background-color:#00b0f0; text-align:right; ">77</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DATE</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">VAL B</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">VAL C</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">VAL D</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">VAL E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">29/08/2019</td><td style="text-align:right; ">55</td><td style="text-align:right; ">42</td><td style="text-align:right; ">52</td><td style="text-align:right; ">25</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">30/08/2019</td><td style="text-align:right; ">80</td><td style="text-align:right; ">86</td><td style="text-align:right; ">38</td><td style="text-align:right; ">74</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">31/08/2019</td><td style="text-align:right; ">27</td><td style="text-align:right; ">80</td><td style="text-align:right; ">75</td><td style="text-align:right; ">86</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">01/09/2019</td><td style="text-align:right; ">96</td><td style="text-align:right; ">40</td><td style="text-align:right; ">5</td><td style="text-align:right; ">37</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">02/09/2019</td><td style="text-align:right; ">81</td><td style="text-align:right; ">10</td><td style="text-align:right; ">52</td><td style="text-align:right; ">85</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">03/09/2019</td><td style="text-align:right; ">77</td><td style="text-align:right; ">42</td><td style="text-align:right; ">100</td><td style="text-align:right; ">55</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">04/09/2019</td><td style="text-align:right; ">91</td><td style="text-align:right; ">26</td><td style="text-align:right; ">89</td><td style="text-align:right; ">46</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">05/09/2019</td><td style="text-align:right; ">70</td><td style="text-align:right; ">69</td><td style="text-align:right; ">36</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">06/09/2019</td><td style="text-align:right; ">12</td><td style="text-align:right; ">61</td><td style="text-align:right; ">46</td><td style="text-align:right; ">95</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">07/09/2019</td><td style="text-align:right; ">64</td><td style="text-align:right; ">80</td><td style="text-align:right; ">38</td><td style="text-align:right; ">49</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffc000; text-align:right; ">08/09/2019</td><td style="text-align:right; ">59</td><td style="background-color:#00b0f0; text-align:right; ">77</td><td style="text-align:right; ">88</td><td style="text-align:right; ">80</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">09/09/2019</td><td style="text-align:right; ">45</td><td style="text-align:right; ">72</td><td style="text-align:right; ">32</td><td style="text-align:right; ">35</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 >A2</td><td >=TODAY()</td></tr><tr><td >C2</td><td >=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))</td></tr><tr><td >E2</td><td >=VLOOKUP(C2,A4:E16,D2,0)</td></tr></table></td></tr></table>

 

[Whiox]

You guys are fantastic - thank you!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.