Which column / month an entry is in

  • Thread starter Thread starter Barbara Wiseman
  • Start date Start date
B

Barbara Wiseman

I have a spreadsheets with the codes on the left months along the top and
amounts (budgets) in the body, in the row of the code they are for and in
the column for the month the budgets are issued.
Like this
Jan Feb Mar .........................Total
A 10
B 20
C 40

What I would like to do is be able to look up a particular code and find
when the budget was issued. Perhaps an index type thing. I would be quite
happy with an extra column which looked at the 12 columns and returned a
number for the column the first entry was in (some have entries in more than
one column for additions, corrections etc).

I have not come up with anything, and would be grateful for any suggestions,
Barbara
 
Assuming this data is in Sheet1,
with B1:M1 containing the months: Jan, Feb, Mar ... Dec
Jan Feb Mar .........................Total
A 10
B 20
C 40
Click to expand...

In Sheet2
---------
In A1 down will be input the codes, e.g.: B, A, C, ..etc

Put in the formula bar for B1:

=IF(A1="","",INDEX(Sheet1!$B$1:$M$1,MATCH(TRUE,(OFFSET(Sheet1!$B$1:$M$1,MATC
H(A1,Sheet1!A:A,0)-1,)<>0),0)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy B1 down

Col B will return the desired months from Sheet1's B1:M1
for the codes entered in col A

For the sample data in Sheet1,
if A1:A3 contains: C, B, A
B1:B3 will return: Mar, Jan, Feb
 
Just a clarification on the alignment of sample values
in the source table in Sheet1. The table actually has:

"10" under Feb for code A
"20" under Jan for code B
"40" under Mar for code C

viz., it actually looks like this:

-- Jan Feb Mar ...
A____10____ ...
B_20_______ ...
C________40 ...
 
Max,
Thank you so much for taking the time to work this out and reply. I have
got it to do exactly what I need. I actually put it on the same sheet,
after playing about with it in the form you set out.
Barbara
 
Back
Top