Need assistance with formula (Array, if, then funcion possibly)

I

ibesmond

New Member
Joined
Nov 26, 2010
Messages
17
Hello,

I need a little assistance determining a formula to use for this problem I am having.

Take the example.

I have my data table. Its a couple of columns

[TABLE="width: 381"]
<TBODY>[TR]
[TD]Folio
[/TD]
[TD]DATE
[/TD]
[TD]Count
[/TD]
[TD]REIM
[/TD]
[TD]R/T
[/TD]
[/TR]
[TR]
[TD]14855</SPAN>
[/TD]
[TD="align: right"]3/16/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]452.00</SPAN>
[/TD]
[TD="align: right"]515.11</SPAN>
[/TD]
[/TR]
[TR]
[TD]52235</SPAN>
[/TD]
[TD="align: right"]3/5/2014</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]316.20</SPAN>
[/TD]
[TD="align: right"]360.35</SPAN>
[/TD]
[/TR]
[TR]
[TD]52707</SPAN>
[/TD]
[TD="align: right"]3/5/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]176.91</SPAN>
[/TD]
[TD="align: right"]201.61</SPAN>
[/TD]
[/TR]
[TR]
[TD]58108</SPAN>
[/TD]
[TD="align: right"]3/14/2014</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]472.78</SPAN>
[/TD]
[TD="align: right"]538.79</SPAN>
[/TD]
[/TR]
[TR]
[TD]59190</SPAN>
[/TD]
[TD="align: right"]3/7/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]164.47</SPAN>
[/TD]
[TD="align: right"]187.43</SPAN>
[/TD]
[/TR]
[TR]
[TD]59652</SPAN>
[/TD]
[TD="align: right"]3/28/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]62.00</SPAN>
[/TD]
[TD="align: right"]70.66</SPAN>
[/TD]
[/TR]
[TR]
[TD]60199</SPAN>
[/TD]
[TD="align: right"]3/6/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]170.93</SPAN>
[/TD]
[TD="align: right"]194.80</SPAN>
[/TD]
[/TR]
[TR]
[TD]62148</SPAN>
[/TD]
[TD="align: right"]3/19/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]178.80</SPAN>
[/TD]
[TD="align: right"]203.76</SPAN>
[/TD]
[/TR]
[TR]
[TD]62535</SPAN>
[/TD]
[TD="align: right"]3/9/2014</SPAN>
[/TD]
[TD="align: right"]11</SPAN>
[/TD]
[TD="align: right"]1,414.31</SPAN>
[/TD]
[TD="align: right"]1,611.78</SPAN>
[/TD]
[/TR]
[TR]
[TD]62596</SPAN>
[/TD]
[TD="align: right"]3/8/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]135.57</SPAN>
[/TD]
[TD="align: right"]154.50</SPAN>
[/TD]
[/TR]
[TR]
[TD]62978</SPAN>
[/TD]
[TD="align: right"]3/4/2014</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]599.25</SPAN>
[/TD]
[TD="align: right"]682.92</SPAN>
[/TD]
[/TR]
[TR]
[TD]63322</SPAN>
[/TD]
[TD="align: right"]3/1/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]158.66</SPAN>
[/TD]
[TD="align: right"]180.81</SPAN>
[/TD]
[/TR]
[TR]
[TD]64045</SPAN>
[/TD]
[TD="align: right"]3/1/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]158.66</SPAN>
[/TD]
[TD="align: right"]180.81</SPAN>
[/TD]
[/TR]
[TR]
[TD]64226</SPAN>
[/TD]
[TD="align: right"]3/4/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]139.29</SPAN>
[/TD]
[TD="align: right"]158.74</SPAN>
[/TD]
[/TR]
[TR]
[TD]64550</SPAN>
[/TD]
[TD="align: right"]3/14/2014</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]312.38</SPAN>
[/TD]
[TD="align: right"]355.99</SPAN>
[/TD]
[/TR]
[TR]
[TD]65176</SPAN>
[/TD]
[TD="align: right"]3/11/2014</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]347.95</SPAN>
[/TD]
[TD="align: right"]396.53</SPAN>
[/TD]
[/TR]
[TR]
[TD]66896</SPAN>
[/TD]
[TD="align: right"]3/11/2014</SPAN>
[/TD]
[TD="align: right"]6</SPAN>
[/TD]
[TD="align: right"]986.10</SPAN>
[/TD]
[TD="align: right"]1,123.78</SPAN>
[/TD]
[/TR]
[TR]
[TD]67596</SPAN>
[/TD]
[TD="align: right"]3/26/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]132.16</SPAN>
[/TD]
[TD="align: right"]150.61</SPAN>
[/TD]
[/TR]
[TR]
[TD]70160</SPAN>
[/TD]
[TD="align: right"]3/18/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]168.26</SPAN>
[/TD]
[TD="align: right"]191.75</SPAN>
[/TD]
[/TR]
[TR]
[TD]70863</SPAN>
[/TD]
[TD="align: right"]3/1/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]158.66</SPAN>
[/TD]
[TD="align: right"]180.81</SPAN>
[/TD]
[/TR]
[TR]
[TD]70876</SPAN>
[/TD]
[TD="align: right"]3/12/2014</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]811.10</SPAN>
[/TD]
[TD="align: right"]924.35</SPAN>
[/TD]
[/TR]
[TR]
[TD]71667</SPAN>
[/TD]
[TD="align: right"]3/14/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]157.00</SPAN>
[/TD]
[TD="align: right"]178.92</SPAN>
[/TD]
[/TR]
[TR]
[TD]71767</SPAN>
[/TD]
[TD="align: right"]3/12/2014</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]508.35</SPAN>
[/TD]
[TD="align: right"]579.33</SPAN>
[/TD]
[/TR]
[TR]
[TD]72125</SPAN>
[/TD]
[TD="align: right"]3/8/2014</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]300.04</SPAN>
[/TD]
[TD="align: right"]341.93</SPAN>
[/TD]
[/TR]
[TR]
[TD]72832</SPAN>
[/TD]
[TD="align: right"]3/14/2014</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]312.38</SPAN>
[/TD]
[TD="align: right"]355.99</SPAN>
[/TD]
[/TR]
[TR]
[TD]72934</SPAN>
[/TD]
[TD="align: right"]3/31/2014</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]238.61</SPAN>
[/TD]
[TD="align: right"]271.92</SPAN>
[/TD]
[/TR]
[TR]
[TD]74163</SPAN>
[/TD]
[TD="align: right"]3/30/2014</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]238.61</SPAN>
[/TD]
[TD="align: right"]271.92</SPAN>
[/TD]
[/TR]
[TR]
[TD]74887</SPAN>
[/TD]
[TD="align: right"]3/5/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]176.91</SPAN>
[/TD]
[TD="align: right"]201.61</SPAN>
[/TD]
[/TR]
[TR]
[TD]74888</SPAN>
[/TD]
[TD="align: right"]3/16/2014</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]279.95</SPAN>
[/TD]
[TD="align: right"]319.04</SPAN>
[/TD]
[/TR]
[TR]
[TD]74967</SPAN>
[/TD]
[TD="align: right"]3/28/2014</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[TD="align: right"]838.53</SPAN>
[/TD]
[TD="align: right"]955.61</SPAN>
[/TD]
[/TR]
[TR]
[TD]75795</SPAN>
[/TD]
[TD="align: right"]3/23/2014</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]125.01</SPAN>
[/TD]
[TD="align: right"]142.46</SPAN>
[/TD]
[/TR]
[TR]
[TD]75971</SPAN>
[/TD]
[TD="align: right"]3/10/2014</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]343.47</SPAN>
[/TD]
[TD="align: right"]391.43</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

From this data I want to setup a formula that would say.
I have another table, with the folio numbers reordered with additional columns. (for example we could say by date, but its not exactly true)

G H I
[TABLE="width: 132"]
<TBODY>[TR]
[TD]63322</SPAN>
[/TD]
[TD="align: right"]3/1/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]64045</SPAN>
[/TD]
[TD="align: right"]3/1/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]70863</SPAN>
[/TD]
[TD="align: right"]3/1/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]62978</SPAN>
[/TD]
[TD="align: right"]3/4/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]64226</SPAN>
[/TD]
[TD="align: right"]3/4/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]52235</SPAN>
[/TD]
[TD="align: right"]3/5/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]52707</SPAN>
[/TD]
[TD="align: right"]3/5/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]74887</SPAN>
[/TD]
[TD="align: right"]3/5/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]60199</SPAN>
[/TD]
[TD="align: right"]3/6/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]59190</SPAN>
[/TD]
[TD="align: right"]3/7/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]62596</SPAN>
[/TD]
[TD="align: right"]3/8/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]72125</SPAN>
[/TD]
[TD="align: right"]3/8/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]62535</SPAN>
[/TD]
[TD="align: right"]3/9/2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]75971</SPAN>
[/TD]
[TD="align: right"]3/10/2014</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Basically the formula I need to be input into column I in this example:
Look at the folio array A2:A33.
If I2 is equal to the folio in the array A2:A33 (Folios), then input the value from column E (R/T), into column I that matches the folio.

So for instance: 63322 would return the value 180.81, 64045 would return the value 180.81 and so forth down the list.

Thank you so much.
I think maybe the vlookup might work but not sure.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
ibesmond,

Try in I2 and copy down ......

=LOOKUP(G2,A$2:A$5000,E$2:E$5000)


Adjust 5000 if required.Hope that helps.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top