Vlookup with a difference

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

=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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
=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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hi Jay,

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

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. :cry:

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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. :cry:

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...
 

Forum statistics

Threads
1,136,712
Messages
5,677,330
Members
419,688
Latest member
sarahmichelle

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
Top