Using LOOKUP with an aray of cells based on data in header row

[YaskinForrit]

I am adding new functionality to a spreadsheet which is used to record amounts paid into a christmas club.

The main sheet has 1 column for week 1 and 2 columns for each other week. (this is to keep a running total and cannot be removed). When someone does not make a payment, it is entered as 0.00

[tt]
No. NAME wk1amt wk2amt wk2tot wk3amt wk3tot
1 *** £10.00 £10.00 £20.00 £0.00 £20.00
2 *** £10.00 £0.00 £10.00 £0.00 £10.00
5 *** £5.00 £5.00 £10.00 £0.00 £10.00
6 *** £5.00 £5.00 £10.00 £5.00 £15.00
7 *** £10.00 £20.00 £30.00 £10.00 £40.00
8 *** £0.00 £5.00 £5.00 £0.00 £5.00
9 *** £0.00 £10.00 £10.00 £0.00 £10.00
10 *** £5.00 £5.00 £10.00 £5.00 £15.00
[/tt]

I need to identify when the person last made a payment.
I have a lookup which returns the last non-zero value: -

=LOOKUP(2,1/(VALUE(C$2C$2)<>0),C$1C$1)

but it only returns the last entry (which is the same for everybody).

Is there any way of restricting the look up array to just use the amt columns?

I would prefer not to use VBA as the PC this will be used on is locked down and macros are disabled in Excel

 

[SkipVought]

Hi,

I'd suggest STORING the data like this...
[tt]
NAME Week Amt
Al 1/1/2009 £10.00
Ben 1/1/2009 £10.00
Chip 1/1/2009 £5.00
Dan 1/1/2009 £5.00
Earl 1/1/2009 £10.00
Harry 1/1/2009 £5.00
Al 1/8/2009 £10.00
Chip 1/8/2009 £5.00
Dan 1/8/2009 £5.00
Earl 1/8/2009 £20.00
Fred 1/8/2009 £5.00
Gary 1/8/2009 £10.00
Harry 1/8/2009 £5.00
Dan 1/15/2009 £5.00
Earl 1/15/2009 £10.00
Harry 1/15/2009 £5.00
[/tt]
Last Payment
[tt]
E F
Last Pmt
Al 1/8/2009
Ben 1/1/2009
Chip 1/8/2009
Dan 1/15/2009
Earl 1/15/2009
Fred 1/8/2009
Gary 1/8/2009
Harry 1/15/2009
[/tt]
here's the formula, using named ranges...
[tt]
F2 =MAX(OFFSET($A$1,MATCH(E2,NAME,0),1,COUNTIF(NAME,E2),1))
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[YaskinForrit]

Unfortunately, I am not able to change the structure, the people using the spreadsheet have been using it in this layout for the last 12 years and refuse to let me change it. (it has taken 3 years to get them to agree to moving it from Lotus 123 to Excel)

 

[GlennUK]

Do you want to identify the column title or the amount? This will give the column header ( assuming that at least one payment has been made ), but it's an array formula which must be entered using Ctrl-Shift-Enter:

=INDEX($A$1:$G$1,MAX(IF(IF(MOD(COLUMN(D2:G2),2)=0,D2:G2,0)>0,COLUMN(D2:G2))))

Note that the data columns reference start at D2, which is the column for wk2amt ( you have no wk1tot, which means that the odd/even pattern of columns doesn't begin until wk2amt ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

I'm not sure you really have to worry about the odd and even structure, since the maximum value in any row must belong to the last week a payment was made, be it in the total row, or the amount row for the first week.

If you want to add a column at the extreme right hand edge of your worksheet indicating the week in which the last payment was made, you can simply use:

=INDEX($1:$1,MATCH(MAX(C2:G2),A2:G2,0))

This formula was taken from row 2, of course, and must be copied down as far as you have entries. You will also need to change "G" to allow as many columns as you have weeks (unless you put the whole thing on a new worksheet where you can simply have 2:2 etc.). Note that this returns the entire header, including tot or amt. You can, of course, tidy it up so it returns just wk3, wk47 or whatever:

=LEFT(INDEX($1:$1,MATCH(MAX(C2:G2),A2:G2,0)), LEN(INDEX($1:$1,MATCH(MAX(C2:G2),A2:G2,0)))-3)

 

[YaskinForrit]

Thanks for the input from all of you.

lionelhill, your index has worked a treat. many thanks

 

[GlennUK]

Yes, lionelhill, you are right in that you don't have to take into consideration the odd/even structure. I must think about the problem before giving a quick answer ( I've done stuff for odd/even structure many times before, due to having a system that posted actual vs budget that way ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

Hey, but we learnt something useful anyway; the odd/even thing is a pretty neat trick.