Copy a cell value based of an array of data

A

abrig005

Board Regular
Joined
Jan 6, 2017
Messages
82
Office Version
  1. 365
Platform
  1. Windows
Hello, thanks in advance for any help!

I am trying to search the array D3:P17 for names listed in B2:B:17 and return the month and FY from cells D2, D3, D4, etc. and put the month and FY in cell A2.

For example, the formula should look at cell B2 then search all the columns in D3:P17 and determine that ex1 happened Oct FY 13.

Any suggestions?
Thanks again!!!!!!!!!!!!!!!!






<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I </th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O </th><th>P</th></tr></thead><tbody>
<tr><td>1</td><td>Month, FY</td><td>Alpha 1</td><td> </td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY13</td><td>FY14</td></tr>
<tr><td>2</td><td>Oct FY 13</td><td>ex 1</td><td> </td><td>Oct FY13</td><td>Nov FY13</td><td>Dec FY13</td><td>Jan FY13</td><td>Feb FY13</td><td>Mar FY13</td><td>Apr FY13</td><td>May FY13</td><td>Jun FY13</td><td>Jul FY13</td><td>Aug FY13</td><td>Sep FY13</td><td>Total FY14</td></tr>
<tr><td>3</td><td>Nov FY 13</td><td>ex 2</td><td> </td><td>ex1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>4</td><td>Dec FY 13</td><td>ex 3</td><td> </td><td>0</td><td>ex2</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>5</td><td>Jan FY 13</td><td>ex 4</td><td> </td><td>0</td><td>0</td><td>ex3</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>6</td><td>Feb FY 13</td><td>ex 5</td><td> </td><td>0</td><td>0</td><td>0</td><td>ex4</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>7</td><td>Mar FY 13</td><td>ex 6</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>ex5</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>8</td><td>Apr FY 13</td><td>ex 7</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>ex6</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>9</td><td>etc.</td><td>ex 8</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>ex7</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>10</td><td>etc.</td><td>ex 9</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>11</td><td>etc.</td><td>ex 10</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>12</td><td>etc.</td><td>ex 11</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>13</td><td>etc.</td><td>ex 12</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>14</td><td>etc.</td><td>ex 13</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>15</td><td>etc.</td><td>ex 14</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>16</td><td>etc.</td><td>ex 15</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>17</td><td>etc.</td><td>ex 16</td><td> </td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td></td></tr>
</tbody></table>
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
alan900sid456
bill#N/Aharry567
clive678big list of names > > > > > > > >clive678
tom789
alan900
tim1011
formula giving 900
=OFFSET($L$1,MATCH(A2,$L$2:$L47,0),1)
easy to stop the n/a for non matches with if(iserror) if so desired

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Month, FYAlpha 1 FY13FY13FY13FY13FY13FY13FY13FY13
Oct FY 13ex 1 Oct FY13Nov FY13Dec FY13Jan FY13Feb FY13Mar FY13Apr FY13May FY13
Nov FY 13ex 2 ex10000000
Dec FY 13ex 3 0ex2000000
Jan FY 13ex 4 00ex300000
Feb FY 13ex 5 000ex40000
Mar FY 13ex 6 0000ex5000
Apr FY 13ex 7 00000ex600
etc.ex 8 000000ex70
etc.ex 9 00000000
etc.ex 10 00000000
etc.ex 11 00000000
etc.ex 12 00000000
etc.ex 13 00000000
etc.ex 14 00000000
etc.ex 15 00000000
etc.ex 16 00000000
is the ex1 always somewhere in column D or can it be for example in Col E

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

vinzent
  • Question
Formula to get the 4th workday before end of month
Replies
8
Views
195
Tetra201
Tetra201
S
  • Question
Sumifs or another sum formula
Replies
6
Views
191
Fluff
Fluff
V
  • Solved
Week Number
Replies
2
Views
142
vmjan02
V
R
  • Solved
Allocate between dates to months then summarise by month and year
Replies
7
Views
277
Eric W
Eric W
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
280
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,679
Messages
6,126,177
Members
449,296
Latest member
tinneytwin

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