Vlookup with a difference

[jaypatel]

Hi,
my column headings are dates which run on a weekly basis..... so cell b1 is 4th October, cell c1 is 11th October etc, increasing by 7 days.......

in Column A is the name of the member. so cell a2= person 1; cell a3 = person2 etc

The data ie cells b2....CA20000 (and will be increasing) is full of whole numbers. The data is how many times they logged onto our website.

The problem: is it possible to tell me going through row by row (member by member) when was the last time they logged on? Of course there could be breaks when they have logged on.... for example:

Person1: Week 1= 45; week 2= 90; week 3= 0; week 4=50; week 5=0
Person 2: week 1=2; week 2=0; week 3=0; week 4=0, week 5=0.


So in this example, the last time this person 1 logged in was week 5
And for person 2 would be week 2.

I think its to do with lookup's but it is very complicated, and would appreciate any advice or scripts that can help.

Regards

Jay

 

[Andrew Poulsom]

The problem: is it possible to tell me going through row by row (member by member) when was the last time they logged on? Of course there could be breaks when they have logged on.... for example:

Person1: Week 1= 45; week 2= 90; week 3= 0; week 4=50; week 5=0
Person 2: week 1=2; week 2=0; week 3=0; week 4=0, week 5=0.

So in this example, the last time this person 1 logged in was week 5
And for person 2 would be week 2.

Please explain why person 1 is week 5 and person 2 is week 2, when week5=0 in both cases.

 

[Domenic]

Shouldn't Person1 equal Week4, and Person2 equal Week1? If so, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(B1:CA1,MATCH(2,1/(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0)))

Hope this helps!

 

[barry houdini]

In your example shouldn't person 1 be week 4 and person 2 week 1 i.e. the last week which wasn't zero?

Do you want to return the actual date from the header row?

Try this formula in CB2 copied down

=LOOKUP(2,1/B2:CA2,B$1:CA$1)

format as date

 

[barry houdini]

=INDEX(B1:CA1,MATCH(2,1/(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0)))

Hi Domenic,

I assumed that a formula was required for each row, but to get the last date for a specific person, "Person1", you could use

=LOOKUP(2,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

 

[Domenic]

=INDEX(B1:CA1,MATCH(2,1/(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0)))

Hi Domenic,

I assumed that a formula was required for each row, but to get the last date for a specific person, "Person1", you could use

=LOOKUP(2,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

Hi Barry!

Since the data in this case will only contain whole numbers, your formula will definitely return the correct result. However, I still prefer my formula. It can be used when data includes decimal numbers. If needed elsewhere, the user can easily adopt it without regard to the data type. And unless I'm mistaken, any potential loss in efficiency is likely negligible.

 

[Domenic]

Actually, in an effort to eliminate all risks, the following formula could be used instead...

=LOOKUP(9.99999999999999E+307,IF(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0,INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)),B1:CA1)

...confirmed with CONTROL+SHIFT+ENTER. This way, in addition to decimal numbers, the data can contain text which would need to be ignored.

 

[barry houdini]

Hi Jay,

Sorry for the formula overkill, just one more from me, I promise

Hi Domenic,

Thanks for your previous comments. As you say my previous formula wouldn't work correctly if there were values between -0.5 and 0.5.

I believe you could use the following formula to ignore all text, zeroes and error values and return the date associated with the last numeric

=LOOKUP(9.9999999999999E+307,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

 

[Domenic]

Hi Domenic,

Thanks for your previous comments. As you say my previous formula wouldn't work correctly if there were values between -0.5 and 0.5.

I believe you could use the following formula to ignore all text, zeroes and error values and return the date associated with the last numeric

=LOOKUP(9.9999999999999E+307,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

Nice one, Barry! However, if I'm not mistaken, 1/Array is less efficient than using the IF statement...