Populate using SUMPRODUCT or ARRAY

[BRISBANEBOB]

Can anyone help with a formula to take data from a column entry to a row style? I've attached a sample spreadsheet.

Thanks for any ideas.

[c_m]

Try:
=IF(AND(C$5>=INDEX(Record!$B$8:$E$14,MATCH($B6,Record!$B$8:$B$14,0),2),C$5<=INDEX(Record!$B$8:$E$14,MATCH($B6,Record!$B$8:$B$14,0),3)),INDEX(Record!$B$8:$E$14,MATCH($B6,Record!$B$8:$B$14,0),4),"")
in C6 of log sheet.
copy down and drag across

[BRISBANEBOB]

Works beautifully! Thanks - logic had me beaten.

[DonkeyOte]

BRISBANEBOB,

You've marked as SOLVED so I might be missing something but as I see it the prior formula does not work in so far as it will only pull the first record for each person (eg 2nd instance of James is ignored)

Based on my understanding I think the below will work for you:

Log!C6:
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",LOOKUP(2,1/((Record!$B$8:$B$14=$B6)*(Record!$C$8:$C$14<=C$5)*(Record!$D$8:$D$14>=C$5)),Record!$E$8:$E$14)))

copied across matrix

[BRISBANEBOB]

Please can you explain how this formula works - there are things in it I didn't know existed...

[DonkeyOte]

It's not quick to explain I'm afraid and I've tried to explain this approach a few times - once here: http://www.excelforum.com/2053930-post10.html ... read through and see if it makes more sense ?

The 2nd LOOKUP with CHOOSE is used to handle the fact that in some cases you may get an error result from the embedded LOOKUP... and in those cases you want to return a Null rather than the Error value to the cell in which the formula exists.

These formulae are not quick performance wise but I would say less expensive than a Sumproduct / CSE approach... I don't have the requisite knowledge to advise definitively.

[BRISBANEBOB]

You are right about being difficult to explain but it does make sense. However, the chance of me ever being able to create it from scratch is somewhat remote...thanks for your help

[BRISBANEBOB]

How does this section work?

LOOKUP(REPT("Z",255),CHOOSE({1,2},

[DonkeyOte]

It works in the same way as outlined in the other thread.

The criteria is set to a big text value (ie Z repeated 255 times).

The lookup_vector is populated using

CHOOSE({1,2},"",LOOKUP(...))

You thus end up with a 2 value lookup_vector with the first value always being a Null ("") and the second value being the result of your embedded LOOKUP formula...

If the embedded LOOKUP formula returns a text value (ie non-error) then that result will be returned by the outer LOOKUP ... if the embedded LOOKUP formula returned an error value then the error value would be ignored and the Null is returned...

Consider: =LOOKUP(REPT("Z,255),CHOOSE({1,2},"","Apple"))

The above will return "Apple" whereas

Consider: =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",#DIV/0!))

The above returns Null given the Error value is ignored -- it is ignored because the data type of the 2nd value does not match the data type of the criteria (in this case: text)... so the last value in the lookup_vector that is of the same data type as the criteria and that is less than the criteria value is Null.

I hope that makes sense.

[BRISBANEBOB]

It does make sense - the learning process never ends.

Thanks for taking the time to explain